Jump to content


Photo

Simple mysql question


  • Please log in to reply
4 replies to this topic

#1 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 12 July 2006 - 06:03 PM

I have another simple mysql question. I am wanting to search a table and print/echo all the results that match what I am looking for into a table. Here is what I have so far. I know that I am missing something important.

<?php
include('db.php');
$get_inbox = mysql_query("SELECT message_id, email, subject, date, status From messages LIMIT 0, 30");
$result = mysql_num_rows($get_inbox);
if ($result == 0) {
    echo "<p><font color=\"#FF0000\">*You have no messages</font></p>";
}else{
    print_r("
<td width=\"20%\"><a href=\"$message_id\">$email</a></td>
<td width=\"20%\">$subject</td>
<td width=\"20%\"><div align=center>$date</div></td>
<td width=\"20%\"><div align=center>$status</div></td>
<td width=\"20%\"><div align=center><i><a href=\"$message_id\">Delete</a></i></div></td>");
}
?>

I am wanting to print/echo everything that matches the search in this table format. Any suggestions on how this can be done would be great.
http://tomfmason.com/test.js

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#2 micah1701

micah1701
  • Members
  • PipPipPip
  • Advanced Member
  • 613 posts
  • LocationEllington, CT USA

Posted 12 July 2006 - 06:10 PM

change:
"SELECT message_id, email, subject, date, status From messages LIMIT 0, 30"

to:
"SELECT message_id, email, subject, date, status From messages WHERE column LIKE '$searchString%'"

//where $searchString is the value you are looking for and % is the wild card

note, you can get rid of the LIMIT arg, unless you want to limit your results to the first 30 returned
"Confidence in the face of risk."

#3 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 12 July 2006 - 06:13 PM

You don't fetch the data after performing the query, try this:
<?php
nclude('db.php');
$q = "SELECT message_id, email, subject, date, status From messages LIMIT 0, 30";
$get_inbox = mysql_query($q); or die("Problem with the query: $q<br>" . mysql_error);
echo '<table>';
while ($rw = mysql_fetch_assoc($get_inbox)) {
    echo '
<tr><td width="20%"><a href="' . $rw['message_id'] . '">' . $rw['email'] . '</a></td>
<td width="20%">' . $rw['subject'] . '</td>
<td width="20%"><div align=center>' . $rw['date'] . '</div></td>
<td width="20%"><div align=center>$rw['status'] . '</div></td>
<td width="20%"><div align=center><i><a href="' . $rw['message_id'] . '">Delete</a></i></div></td></tr>';
}
echo '</table>';
?>

Ken

#4 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 12 July 2006 - 06:14 PM

EDIT:  i've been robbed of my reply (thanks ken :)), so i'll just add the first and last paragraph from my original comment.

first off, print_r() is for arrays only, not strings.

there are a lot of FAQs and snippets and topics about simple MySQL data manipulation and handling, have a look and you'll find everything you need to know about it.

#5 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 12 July 2006 - 08:09 PM

when I firsted tried your code I got the following error.

Parse error: parse error, unexpected T_LOGICAL_OR in test.php on line 4


with your suggestion to use mysql_fetch_assoc I was able to look in the manual and figure it out.
Also I gave you this code out of context here is the full code.

<?php
include('db.php');
$message_new_sql= sprintf("SELECT COUNT(*) AS message_new FROM `messages` WHERE `status` ='new'"); 
$message_old_sql= sprintf("SELECT COUNT(*) AS message_old FROM `messages` WHERE `status` ='old'");
$message_new_res= mysql_query($message_new_sql) or die(mysql_error()); 
$message_old_res= mysql_query($message_old_sql) or die(mysql_error());
$message_new= mysql_result($message_new_res, 0, 'message_new'); 
$message_old= mysql_result($message_old_res, 0, 'message_old');
$amount ="You have <b>$message_new</b> new and <b>$message_old</b> old messages.";
?>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Your Messages</title>
</head>

<body>
<div align=center>
<h3>Your Messages</h3>
</div>
<div align=right>
<p><?php echo $amount; ?></p>
</div>
<p><h4>Messages</h4></p>
<table border="1" width="100%">
	<tr>
		<td width="20%"><div align=center><b>From</b></div></td>
		<td width="20%"><div align=center><b>Subject</b></div></td>
		<td width="20%"><div align=center><b>Date Sent</b></div></td>
		<td width="20%"><div align=center><b>Status</b></div></td>
		<td width="20%"><div align=center><b>Mail Options</b></div></td>
	</tr>
	<tr>
<?php

$q = "SELECT message_id, email, subject, date, status From messages LIMIT 0, 30";
$get_inbox = mysql_query($q);
if (!$get_inbox) {
   echo "Could not successfully run query ($sql) from DB: " . mysql_error();
   exit;
}
if (mysql_num_rows($get_inbox) == 0) {
   echo "<p><tb><font color=\"#FF0000\">*Your Inbox is empty</font></tb></p>";
   exit;
}         
while ($rw = mysql_fetch_assoc($get_inbox)) {
     if ($status == 'new') {
          $color = "#FF0000";
     }else{
          $color = "#000000";
     }         
    echo '
<tr><td width="20%"><div align=center><a href="' . $rw['message_id'] . '">' . $rw['email'] . '</a></div></td>
<td width="20%"><div align=center>' . $rw['subject'] . '</div></td>
<td width="20%"><div align=center>' . $rw['date'] . '</div></td>
<td width="20%"><div align=center><font color=$color><b>' . $rw['status'] . '<b></font></div></td>
<td width="20%"><div align=center><i><a href="' . $rw['message_id'] . '">Delete</a></i></div></td></tr>';
}
mysql_free_result($get_inbox);
?>	
	</tr>
</table>
<p><font color="#FF0000" size="2"><i>*You can read a message by clicking on the email address</i></font></p>
</body>

</html>

here is the relativent piece of code

$q = "SELECT message_id, email, subject, date, status From messages LIMIT 0, 30";
$get_inbox = mysql_query($q);
if (!$get_inbox) {
   echo "Could not successfully run query ($sql) from DB: " . mysql_error();
   exit;
}
if (mysql_num_rows($get_inbox) == 0) {
   echo "<p><tb><font color=\"#FF0000\">*Your Inbox is empty</font></tb></p>";
   exit;
}         
while ($rw = mysql_fetch_assoc($get_inbox)) {
     if ($status == 'new') {
          $color = "#FF0000";
     }else{
          $color = "#000000";
     }         
    echo '
<tr><td width="20%"><div align=center><a href="' . $rw['message_id'] . '">' . $rw['email'] . '</a></div></td>
<td width="20%"><div align=center>' . $rw['subject'] . '</div></td>
<td width="20%"><div align=center>' . $rw['date'] . '</div></td>
<td width="20%"><div align=center><font color=$color><b>' . $rw['status'] . '<b></font></div></td>
<td width="20%"><div align=center><i><a href="' . $rw['message_id'] . '">Delete</a></i></div></td></tr>';
}
mysql_free_result($get_inbox);
?>

Thanks for pointing me in the right direction.
         

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users