Jump to content

Archived

This topic is now archived and is closed to further replies.

tomfmason

Simple mysql question

Recommended Posts

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.

[code=php:0]<?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>");
}
?>[/code]

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.
[img]http://tomfmason.com/test.js[/img]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
You don't fetch the data after performing the query, try this:
[code]<?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>';
?>[/code]

Ken

Share this post


Link to post
Share on other sites
[b]EDIT:  i've been robbed of my reply (thanks ken :)), so i'll just add the first and last paragraph from my original comment.[/b]

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.

Share this post


Link to post
Share on other sites
when I firsted tried your code I got the following error.

[code]Parse error: parse error, unexpected T_LOGICAL_OR in test.php on line 4[/code]


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.

[code=php:0]<?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>
[/code]

here is the relativent piece of code

[code=php:0]$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);
?>[/code]

Thanks for pointing me in the right direction.
         

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.