PHP Gurus - Quick Question

[novon]

I was successful setting up the mySQL db, adding items to it, and displaying all enteries in a table. I was wondering if I want to display a specific item, such as if the selected topic is "crabs" display only the crab info from the db, what code would I use that searches the db for row "crabs" and displays only that content? Anyone got some sample code?

thanks

 

[splice]

You have to do an SQL statement to get all the crab fields.

"SELECT crabs FROM crab_database"

or, if you want to select only unique entries in the crabs field...

"SELECT DISTINCT crabs FROM crab_database"

 

[Moonbender]

Hmn. Correct me if I'm wrong, but those two queries would still return all rows in the table, just limited to one column, no? If you only want to return one row, you need to have a WHERE clause in the statement, as in:

SELECT * FROM crustaceans WHERE name='crabs'

This would return all columns of those rows in the table 'crustaceans' where the column name is equal to 'crabs'. Or did I misunderstand what novon wants to do?

 

[novon]

Okay, well, here is what I tried, but I'm not getting any results back from the db. I want to take the variable "viewTopic" search for it, and display content (topicText, topicDate) from the row with the name = "viewTopic". Help?

-----------------------------------------------------
<?php
elseif (isset($viewTopic)):
// If user wants to view a topic named variable "viewTopic"
// Connect to the database server
$dbcnx = @mysql_connect("localhost", "login", "password");
if (!$dbcnx) {
echo( "<p>Unable to connect to the " .
"database server at this time.</p>" );
exit();
}
// Select the topics database
if (! @mysql_select_db("topics") ) {
echo( "<p>Unable to locate the " .
"database at this time.</p>" );
exit();
}
// Get topic content

echo( "<p>Topic: " . $viewTopic . "</p>" );
$topicContent = @mysql_query("SELECT * FROM topics WHERE topicName=" . $viewTopic);
echo("<p>Text: " . $topicContent["topicText"] . "</p>");
?>

 

[Moonbender]

The mysql_query() function returns a resource, not an array. You need to use mysql_fetch_array() on the resource returned by mysql_query() to retrieve the actual row. This makes more sense when your query returns more than one row. Typically, a mysql select looks something like this:

$query = mysql_query("SELECT col_A FROM tbl_B WHERE col_C='$D'");

while ($query_array = mysql_fetch_array($query))
{
do_something_to($query_array);
}

This kind of treatment generally works, it also should work for what you want (with different names, obviously). In the special case when you know that there can only be one returned row (such as when you query for a unique id), you can shortcut this to:
$query_array = mysql_fetch_array(mysql_query($query_string));

In any event, read up on the PHP manual on mysql_query and mysql_fetch_array. Another mistake in your code (corrected above) is that you should enclose $viewTopic in quotes when you put it in the SQL query, ie. "SELECT * FROM topics WHERE topicName='" . $viewTopic . "'" or better "SELECT * FROM topics WHERE topicName='$viewTopic'" (remember you can inline variables into double-quoted strings). You do not have to quote numeric variables (like ids) when passing them to MySQL, although MySQL has no problem with it, either.

 

[novon]

you are cool

 

[novon]

One more question I hope you can answer, In the following code, the date colum is sorted by the mySQL "ORDER BY" command from oldest to newest date. Is there a way to reverse this output? Also, how can I format the date to something like "September 9, 2002" rather than "2002-09-09" which is outputed by default?

Thanks man

------------------------------------------
echo("<table width='500' border='1' cellspacing='0' cellpadding='5'><tr>");
// Request the name and date of all the topics
if (isset($sortDate)) { // Sort by Date
echo("<td><b><a href=$PHP_SELF>Topic</a></b></td>
<td><b>Date</b></td>
</tr>");
$result = @mysql_query("SELECT topicName, topicDate FROM topics ORDER BY topicDate");

} else { // Sort by Name Alpha
echo("<td><b>Topic</b></td>
<td><b><a href=$PHP_SELF?sortDate=true>Date</a></b></td>
</tr>");
$result = @mysql_query("SELECT topicName, topicDate FROM topics ORDER BY topicName");
}
while ( $row = mysql_fetch_array($result) ) {
$topicName = $row["topicName"];
$topicDate = $row["topicDate"];

echo("<tr><td width='60%'><a href='$PHP_SELF?viewTopic=" . $topicName . "'>" . $topicName . "</a></td>");
echo("<td width='40%'>" . $topicDate . "</td></tr>");
}

echo("</table>");

 

[Moonbender]

You can control the order using the keyword ASC and DESC (ie, order ascending and descending respectively). ASC is the default, so in this case your query would be: "SELECT topicName, topicDate FROM topics ORDER BY topicDate DESC"
On the date issue, I assume you're saving the dates as MySQL dates? If you do so, change it. 😉
You'll want to work with timestamps, which are regular integers saving the number of seconds since the start of the UNIX Epoch. They offer a pretty easy way to work with dates, but more importantly, most PHP functions use them. If you've got a timestamp, you can use the function date() to format a string, for example (IIRC - refer to the manual for details/corrections) date("F j, Y", $timestamp) would return a string formatted the way you want. As it is now, it's not really a problem either, it just introduces another step, using the function strtotime() to convert your time string "2002-09-09" into its timestamp version.

Some code:
if ($saved_to_db_as_timestamp) $timestamp = $query_array['timestamp'];
else $timestamp = strtotime($query_array['sqldate']);

echo date("F j, Y", $timestamp);

😎