PHP - MYSQL: 2012-09-09

Monday, September 10, 2012

PHP MySQL Select

The SELECT statement is used to select data from a database.

Select Data From a Database Table

The SELECT statement is used to select data from a database.

Syntax

SELECT column_name(s)
FROM table_name

To learn more about SQL, please visit our SQL tutorial.
To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

Example

The following example selects all the data stored in the "Persons" table (The * character selects all the data in the table):

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

mysql_close($con);
?>

The example above stores the data returned by the mysql_query() function in the $result variable.
Next, we use the mysql_fetch_array() function to return the first row from the recordset as an array. Each call to mysql_fetch_array() returns the next row in the recordset. The while loop loops through all the records in the recordset. To print the value of each row, we use the PHP $row variable ($row['FirstName'] and $row['LastName']).
The output of the code above will be:

Peter Griffin
Glenn Quagmire

Display the Result in an HTML Table

The following example selects the same data as the example above, but will display the data in an HTML table:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

The output of the code above will be:

Firstname	Lastname
Glenn	Quagmire
Peter	Griffin

PHP MySQL Insert

The INSERT INTO statement is used to insert new records in a table.

Insert Data Into a Database Table

The INSERT INTO statement is used to add new records to a database table.

Syntax

It is possible to write the INSERT INTO statement in two forms.
The first form doesn't specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1, value2, value3,...)

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

To learn more about SQL, please visit our SQL tutorial.To get PHP to execute the statements above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

Example

In the previous chapter we created a table named "Persons", with three columns; "Firstname", "Lastname" and "Age". We will use the same table in this example. The following example adds two new records to the "Persons" table:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin',35)");

mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Glenn', 'Quagmire',33)");

mysql_close($con);
?>

Insert Data From a Form Into a Database

Now we will create an HTML form that can be used to add new records to the "Persons" table.
Here is the HTML form:

<html>
<body>

<form action="insert.php" method="post">
Firstname: <input type="text" name="firstname" />
Lastname: <input type="text" name="lastname" />
Age: <input type="text" name="age" />
<input type="submit" />
</form>

</body>
</html>

When a user clicks the submit button in the HTML form in the example above, the form data is sent to "insert.php".
The "insert.php" file connects to a database, and retrieves the values from the form with the PHP $_POST variables.
Then, the mysql_query() function executes the INSERT INTO statement, and a new record will be added to the "Persons" table.
Here is the "insert.php" page:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con);
?>

How to Find and Replace Text in MySQL Database using SQL

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)
MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.

For example:
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);
update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)
The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.

Another example:
SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);
Above statement will return ‘WwWwWw.mysql.com’ as result.

Sunday, September 9, 2012

PHP Function array_flip()

Syntax

array array_flip ( array $input );

Definition and Usage

array_flip() returns an array in flip order, i.e. keys from input become values and values from input become keys.
If a value has several occurrences, the latest key will be used as its values, and all others will be lost.

Paramters

Parameter	Description
input	The array to be fliped

Return Values

Returns FALSE if it fails otherwise fliped array.

Example

Try out following example:

<?php
$array = array("a"=>1, "b"=>2, "c"=>3, "d"=>4, "e"=>5);

print_r(array_flip($array));
?>

This will produce following result:

Array ( [1] => a [2] => b [3] => c [4] => d [5] => e)

PHP Function array_filter()

Syntax

array array_filter ( array $input [, callback $callback] );

Definition and Usage

Iterates over each value in the input array passing them to the callback function. If the callback function returns true, the current value from input is returned into the result array. Array keys are preserved.

Paramters

Parameter	Description
input	The array to iterate over
callback	The callback function to use

If no callback is supplied, all entries of input equal to FALSE will be removed.

Return Values

Returns the filtered array.

Example

Try out following example:

<?php
function odd($var)
{
    return($var & 1);
}

function even($var)
{
    return(!($var & 1));
}

$array1 = array("a"=>1, "b"=>2, "c"=>3, "d"=>4, "e"=>5);
$array2 = array(6, 7, 8, 9, 10, 11, 12);

echo "Odd :\n";
print_r(array_filter($array1, "odd"));
echo "Even:\n";
print_r(array_filter($array2, "even"));
?>

This will produce following result:

Odd :
Array ( [a] => 1 [c] => 3 [e] => 5)
Even:
Array ( [0] => 6 [2] => 8 [4] => 10 [6] => 12)

PHP Function array_fill_keys()

Syntax

array array_fill_keys ( array $keys, mixed $value );

Definition and Usage

Fills an array with the value of the value parameter, using the values of the keys array as keys.

Paramters

Parameter	Description
keys	Array of values that will be used as keys
value	Either an string or an array of values

Return Values

Returns the filled array

Example

Try out following example:

<?php
$keys = array('foo', 5, 10, 'bar');
$a = array_fill_keys($keys, 'banana');
print_r($a)
?>

This will produce following result:

Array
(
    [foo] => banana
    [5] => banana
    [10] => banana
    [bar] => banana
)

PHP Function array_fill()

Syntax

array array_fill ( int $start_index, int $num, mixed $value );

Definition and Usage

Fills an array with num entries of the value of the value parameter, keys starting at the start_index parameter.

Paramters

Parameter	Description
start_index	The first index of the returned array
num	Number of elements to insert
value	Values to use filling

Return Values

Returns the filled array

Example

Try out following example:

<?php
$a = array_fill(5, 6, 'apple');
print_r($a)
?>

This will produce following result:

Array
(
    [5]  => apple
    [6]  => apple
    [7]  => apple
    [8]  => apple
    [9]  => apple
    [10] => apple
)

Monday, September 10, 2012

Select Data From a Database Table

Syntax

Example

Display the Result in an HTML Table

Insert Data Into a Database Table

Syntax

Example

Insert Data From a Form Into a Database

How to Find and Replace Text in MySQL Database using SQL

Sunday, September 9, 2012

Syntax

Definition and Usage

Paramters

Return Values

Example

Syntax

Definition and Usage

Paramters

Return Values

Example

Syntax

Definition and Usage

Paramters

Return Values

Example

Syntax

Definition and Usage

Paramters

Return Values

Example

Labels

Popular Posts

Popular Posts