[SOLVED] Retrieving Info from a database

flowingwindrider · July 16, 2007

I would like the code below to fetch and display in an html table the Ad_Id, Date, and Type_Job fields from the 1st 10 rows of a table called employment. I'd like the results to be sorted first by date and then by the Ad_Id #. I have replaced my username and password with "user" and "password". Currently, the script returns the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Also, the script is ignoring the css info that I have supplied.

Any thoughts?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
.style1{
font-family:"Poor Richard";
font-weight:normal;
font-size:14pt;
text-align:center;
background-color:#FFFFFF;
}
.style2{
font-family:"Poor Richard";
font-size:12pt;
background-color:#57A8F9;
text-align:left;
}
.style3{
font-family:"Poor Richard";
font-size:12pt;
background-color:#57A8F9;
text-align:center;
}
</style>
</head>

<body style="background-color: #0ACCF5; background-image: url('../images/InsetBackground.jpg')">

<?php

$dbh = mysql_connect("localhost", "user", "password");

if (!$dbh)
  	{
echo "Error: Could not connect to database. Please try again later.";
exit;
}

mysql_select_db("bransone_classifieds");

$result = mysql_query("SELECT Ad_Id, Date, Type_Job FROM employment LIMIT 10 ORDER BY Date, Ad_Id DESC");

echo "<table border=0 cellpadding=2 cellspacing=2 width=100%><span class=\"style1\">
	<tr>
		<td style=\"width: 10%\">#</td>
		<td>Subject</td>
		<td style=\"width: 20%\">Posted On</td>
		<td style=\"width: 15%\">Details</td>
	</span></td>";

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

?>
</body>
</html>

As always, thanks in advance for your help.

roopurt18 · July 16, 2007

The error is telling you that your SELECT query isn't working properly.

Did you try and plug the query directly into phpMyAdmin? Are you positive that your msql_select_db call is successful? Are you sure that you actually established a connection to the DB?

teng84 · July 16, 2007

$result = mysql_query("SELECT Ad_Id, Date, Type_Job FROM employment LIMIT 10 ORDER BY Date, Ad_Id DESC");

that should be

$result = mysql_query("SELECT Ad_Id, Date, Type_Job FROM employment ORDER BY Date, Ad_Id DESC LIMIT 10");

limit always at the end

paul2463 · July 16, 2007

try these lines instead

mysql_select_db("bransone_classifieds") or die ("could not select the database");

$result = mysql_query("SELECT Ad_Id, Date, Type_Job FROM employment LIMIT 10 ORDER BY Date, Ad_Id DESC") or die ("Error in query" . mysql_error());

it is always a good idea to get the system to throw errors, it lets you know where it is failing

trq · July 16, 2007

Date is a reserved word, you'll need to use `backticks` to escape it.

$result = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date`, Ad_Id DESC LIMIT 10") or die ("Error in query" . mysql_error());

flowingwindrider · July 17, 2007

Thanks all, that got me much further down the path. I can see where adding the error returns makes it easier to tell where the problem lies.

One thing still doesn't add up for me though:

The results are properly sorting in descending order with the Ad IDs, but they don't sort by date correctly. In other words, the Ads sort 10,9,8, etc, but Ads posted on 07/17 display AFTER Ads posted on 07/15.


<?php

$dbh = mysql_connect("localhost", "user", "password");

if (!$dbh)
  	{
echo "Error: Could not connect to database. Please try again later.";
exit;
}

mysql_select_db("bransone_classifieds") or die ("could not select the database");


$result = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date`, Ad_Id DESC LIMIT 10") or die ("Error in query" . mysql_error());

echo "<table border=0 cellpadding=2 cellspacing=2 width=100%>
	<tr class=\"style1\">
		<td style=\"width: 10%\">#</td>
		<td>Subject</td>
		<td style=\"width: 20%\">Posted On</td>
		<td style=\"width: 15%\">Details</td>
	</tr>";

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

?>

Thoughts?

paul2463 · July 17, 2007

you could always try

$result = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` DESC,  Ad_Id DESC LIMIT 10") or die ("Error in query" . mysql_error());

or

$result = mysql_query("SELECT Ad_Id, `Date`, Type_Job FROM employment ORDER BY `Date` ASC, Ad_Id DESC LIMIT 10") or die ("Error in query" . mysql_error());

how is the date formatted in the date column??

flowingwindrider · July 17, 2007

That did the trick! I didn't realize that you have to specify for each column being sorted whether you want it ascending or descending. As always, you guys (and gals) come through for me.

Now I just have to figure out how to make records 11-20, 21-30, and so on appear on their own pages that the server generates as needed. It's going to be a long day.

Sign In

[SOLVED] Retrieving Info from a database

Recommended Posts

flowingwindrider

Link to comment

Share on other sites

roopurt18

Link to comment

Share on other sites

teng84

Link to comment

Share on other sites

paul2463

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

flowingwindrider

Link to comment

Share on other sites

paul2463

Link to comment

Share on other sites

flowingwindrider

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information