Querry MySQL Database and Return Multiple Results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
SalientAnimal
Forum Newbie
Posts: 11
Joined: Mon May 31, 2010 10:01 am

Querry MySQL Database and Return Multiple Results

Post by SalientAnimal »

Hi Everyone,

Well I have a bit of a problem... I have created a search page that I would like to return results from my MySQL database. However for some reason I can not get my page to display the results. Instead I am getting an error page. Below is the code I am using:

In addition to this, should I wish for a user to be able to edit the returned results by clicking a link, how would I do that?

Code: Select all

<?php
session_start();
?>
<link rel="stylesheet" type="text/css" href="css/layout.css"/>
<html>

<?php
$record = $_POST['record'];
echo "<p>Search results for: $record<br><BR>";

$host = "localhost";
$login_name = "root";
$password = "P@ssword";

//Connecting to MYSQL
MySQL_connect("$host","$login_name","$password");

//Select the database we want to use
mysql_select_db("schedules_2010") or die("Could not find database");

$result = mysql_query("SELECT * 
FROM schedule_september_2010 
WHERE 
champ LIKE '%$record%' ") 
or die(mysql_error());  


// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row
echo "<br><p>Your Schedule<BR></p><br>";
echo "<table border=1>\n";
echo "<tr>
<td bgcolor=#444444 align=center><p><b>Champ</p></td>
<td bgcolor=#444444 align=center><p><b>Date</p></td>
<td bgcolor=#444444 align=center><p><b>Start Time</p></td>
<td bgcolor=#444444 align=center><p><b>End Time</p></td>
<td bgcolor=#444444 align=center><p><b>Department</p></td>
<td bgcolor=#444444 align=center><p><b>First Break</p></td>
<td bgcolor=#444444 align=center><p><b>Second Break</p></td>
<td bgcolor=#444444 align=center><p><b>Login ID</p></td>
</tr>\n";
do {




printf("<tr>
<td><p>%s</p></td>
<td><p>%s</p></td>
<td><p>%s</p></td>
<td><p>%s</p></td>
<td><p>%s</p></td>
<td><p>%s</p></td>
<td><p>%s</p></td>
<td><p>%s</p></td>
</tr>\n"
, $row["champ"]
, $row["date_time"]
, $row["start_time"]
, $row["end_time"]
, $row["department"]
, $row["first_break"]
, $row["second_break"]
, $row["login_id"]
);

} while ($row = mysql_fetch_array($result));
echo "</table>\n";
} else {
echo "$champ No Records Found"; 
} 

mysql_free_result($result);
mysql_close($con);
?>
</html>
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Querry MySQL Database and Return Multiple Results

Post by mecha_godzilla »

I can't see anything wrong with your query so it would be helpful if you could say what the error is. If you have access to PHPMyAdmin, it's worth copying-and-pasting the query into the 'SQL' window to see whether the query is correct or the problem is with the way you're connecting to the database.

I usually do my MySQL queries like this:

Code: Select all

$conn = mysql_connect("db_name","username","password");
$sql = "SELECT * FROM products WHERE name LIKE '%$search_string%'";
$result = mysql_query($sql, $conn) or die("No database found");
$number_of_results = mysql_num_rows($result);

if ($number_of_results != 0) {
    while ($result_array = mysql_fetch_array($result, MYSQL_ASSOC)) {
        // do something
    }
}
HTH,

Mecha Godzilla
kristen
Forum Newbie
Posts: 14
Joined: Tue Sep 07, 2010 5:51 pm

Re: Querry MySQL Database and Return Multiple Results

Post by kristen »

mysql_fetch_array does not just fetch a single line of data like mysql_fetch_object

fetch_array (and fetch_assoc) get multiple rows of data for a column, so each column you call generates an array for each column of data.

IE:

Code: Select all

$query = mysql_query("SELECT age, name, nickname FROM test");
WHILE ($row = mysql_fetch_array($query)) {

$age[] = $row['age'];
$name[] = $row['name'];
$nickname[] = $row['nickname'];
}
So $age, $name and $nickname are all arrays. You cannot access the values in the array through $row['age'] in this case. You'd need to know the number of the key you wanted to access, eg: $age[3].

For your purposes you don't really need to know what sits in what key, you just want to iterate over all the rows to create a nice output.

You can easily iterate over an array like so:

Code: Select all

$counter = count($age);

$k = 0;

WHILE ($k <= $counter) {

echo $age[$k];
$k++;
}
You can pair the values like so:

Code: Select all


WHILE ($k <= $counter) {

echo $age[$k], $name[$k], $nickname[$k];
$k++;
}
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Querry MySQL Database and Return Multiple Results

Post by mecha_godzilla »

I'll just add to Kristen's very good advice by saying that you need to make sure you do a test inside your while() loop to make sure the main table tags (<table> and </table>) are only written once.

The way I do this is to create a variable called $counter at the top of the script and assign it a value of 0. When your script enters the while() loop, you need to check if $counter = 0 and if so echo out the opening table tag (<table>) then increment the $counter value (using $counter++). The next time the loop is entered again, the value of $counter will be 1 and the table code won't run. To generate the closing table tag (</table>) you need to check whether $counter is equal to the number of results that were returned.

HTH,

M_G
SalientAnimal
Forum Newbie
Posts: 11
Joined: Mon May 31, 2010 10:01 am

Re: Querry MySQL Database and Return Multiple Results

Post by SalientAnimal »

Hi Guys/Gals,

Thanks for your replies to my post. The information is really valuble. However I'm still stuck with the problem of the results not displaying. The query itself runs perfectly, but for some reason the page layout seems to be causing the problem, which I still haven't been able to figure out.

Oh almost forgot to mention, the error is a normal internet explorer error page, HTTP 500
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Querry MySQL Database and Return Multiple Results

Post by mikosiko »

As I told you in the "other side"... you have several errors in your code.... this a little example:

by the end of your code you have this lines (fragment):

Code: Select all

} while ($row = mysql_fetch_array($result));
echo "</table>\n";
} else {
echo "$champ No Records Found"; 
}

mysql_free_result($result);
mysql_close($con);
?>
tell me ... where is the IF that open the } else that you have in those lines?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Querry MySQL Database and Return Multiple Results

Post by mikosiko »

kristen wrote:....
IE:

Code: Select all

$query = mysql_query("SELECT age, name, nickname FROM test");
WHILE ($row = mysql_fetch_array($query)) {

$age[] = $row['age'];
$name[] = $row['name'];
$nickname[] = $row['nickname'];
}
So $age, $name and $nickname are all arrays. You cannot access the values in the array through $row['age'] in this case. You'd need to know the number of the key you wanted to access, eg: $age[3].


:?: :?: :?: what ? .... :?: :?: :?:

just take a look to the manual's examples....
http://php.net/manual/en/function.mysql-fetch-array.php
Post Reply