Jump to content

Simple mysql question


tomfmason

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]
Link to comment
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
Link to comment
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
Link to comment
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.
Link to comment
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.
         
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.