This is a basic tutorial to teach newbies how to query a MySQL database using PHP, and how to display the results.
Here is the query code itself: Code:
$query = "SELECT * FROM Tablename WHERE Fieldname='".$variable."' ORDER BY Fieldname LIMIT 5"; $result = @mysql_query ($query);
This query must be manipulated according to the structure of the database in question. “Tablename” must be edited to reflect the actual name of an existing table, whereas “Fieldname” must also be changed to reflect a field in the table.
This query is also dynamic, because it searches for Fieldname matches to the variable “$variable”. Therefore you could supply this variable using a form like this: HTML Code:
<form action=form.php method=post> <input type=text name=[b]variable[/b]> <input type=submit value=Submit name=submit> </form>
Pay attention to the other 2 specifications in the query’s code. “ORDER BY Fieldname”, as you may guess, tells the script in what order the results should be displayed. It can be changed to any field that exists in the table.
“LIMIT 5” puts a ceiling on the amount of results that the query can fetch, and can obviously be changed to whatever is required. Using some advanced PHP you can use the LIMIT function to paginate mass amounts of data, but I wont cover that here.
Now that we’ve executed our query, we’ll want to make sure it worked, and that it found some results. We do this with a function called mysql_num_rows: Code:
$num = mysql_num_rows ($result);
This code creates a variable to store the amount of rows (or results) that the query affected.
One thing to note is that if your query finds no results and you still try to display results, you will hit some nasty errors. Therefore I like to install a simple check to make sure we did get a result using an if statement: Code:
if ($num > 0) {
Explained in plain english: If num is above 0, (and therefore at least 1 result was found) then do the following. Bear in mind that we have to close the bracket later to obey PHP syntax.
So here comes the more tricky part of fetching the results. We’re going to use a loop function called while() to do something for each result we find. Code:
while ($row = mysql_fetch_array($result)) { echo $row[Fieldname]; echo $row[AnotherFieldname]; }
This is a bit more complex. It turns each field name into a unique array that is stored in $row[Fieldname], so whatever is in that particular row in the database under that Fieldname will be stored there. You don’t have to have the echo statements in there, they’ll just prove that the code has worked so far.
And finally we have to close the open if: Code:
} else { // The query didnt find any results, so: echo "No results found"; }
I hope this tutorial was useful to you! Good luck with PHP and MySQL.
Add to document.write(“Del.icio.us”) | DiggThis | Yahoo! My Web
Technorati:
Thomas White of ShortyWhite.com.
