$query returning no database results

randal_138 · January 9, 2011

Hello all,

Trying to figure out how to display database results so that they are numbered (for editing and deletion purposes). I want to be able to edit the message text and update the database information with the UPDATE command, but have not gotten that far yet.

Current problem is that I am returning no results. Here is my script:

<!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=iso-8859-1" />
<title>My Profile</title>
<link href="loginmodule.css" rel="stylesheet" type="text/css" />
</head>
<body>
<h1>My Profile </h1>
<a href="member-index.php">Home</a> | <a href="member-profile.php">My Profile</a> | Update Posts | <a href="logout.php">Logout</a>

<br /><br />

<?php
$subject = $_POST['subject'];
$message_text = $_POST['message_text'];

//Connect to mysql server
$link = mysql_connect('XXXXXX', 'XXXXXX', 'XXXXXX');
if(!$link)
{
   die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db('ryan_iframe');
if(!$db)
{
   die("Unable to select database");
}

// validate incoming values
$subject = (isset($_GET['subject'])) ? (int)$_GET['subject'] : 0;
$message_text = (isset($_GET['message_text'])) ? (int)$_GET['message_text'] : 0;

ob_start();

$id = $_GET['SUBJECT'];
$query = sprintf( "
SELECT
   SUBJECT, MSG_TEXT, UNIX_TIMESTAMP(MSG_DATE) AS MSG_DATE
FROM
   FORUM_MESSAGE
WHERE
   SUBJECT = '$id'
ORDER BY
   MSG_DATE DESC",
   DB_DATABASE,
   DB_DATABASE,
   $subject,
   $subject);
$result = mysql_query($query) or die(mysql_error());
$num = mysql_numrows($result);
mysql_close();

$i = 0;
while ($i < $num)
{
   $subject = mysql_result($result, $i, "SUBJECT");
   $message_text = mysql_result($result, $i, "MSG_TEXT");
   
   echo '<div style="width: 400px;padding:20px;">';
   echo '<table border=0 width="400px">';
   echo '<tr>';
   echo '<td style="vertical-align:top;width:auto;">';
   echo 'Date: ';
   echo '</td>';
   echo '<td style="vertical-align:top;width:320px;">';
   echo date('F d, Y', $row['MSG_DATE']) . '</td>';
   echo '</tr>';
   echo '<tr>';
   echo '<td style="vertical-align:top;width:auto;">';
   echo 'Subject: ';
   echo '</td>';
   echo '<td style="vertical-align:top;width:320px;">';
   echo '<div>' . htmlspecialchars($row['SUBJECT']) . '</div>';
   echo '</td>';
   echo '</tr>';
   echo '<tr>';
   echo '<td style="vertical-align:top;width:auto;">';
   echo 'Message: ';
   echo '</td>';
   echo '<td style="vertical-align:top;width:320px;">';
   echo '<div>' . htmlspecialchars($row['MSG_TEXT']) . '</div>';
   echo '</td>';
   echo '</tr>';
   echo '<tr>';
   echo '<td style="vertical-align:top;width:auto;">';
   echo '</td>';
   echo '<td style="vertical-align:top;width:320px;text-align:center;">';
   echo '<form method="post">';
   echo '<input type="hidden" name="update" value="true" />';
   echo '<input type="submit" value="Update" />';
   echo '         ';
   echo '<input type="hidden" name="delete" value="true" />';
   echo '<input type="submit" value="Delete" />';
   echo '</form>';
   echo '</td>';
   echo '</tr>';
   echo '</table>';
   echo '<br />';
   echo '<hr />';
   echo '</div>';
   
      ++$i;
}

?>

</body>
</html>

Thanks in advance.

Ryan

Pikachu2000 · January 9, 2011

When posting code, please enclose it in the forum's

 . . .

BBCode tags.

randal_138 · January 11, 2011

I assume I just do:


<?php
...
?>

Didn't know about this, my bad...

Pikachu2000 · January 11, 2011

Yup, thanks.

PFMaBiSmAd · January 11, 2011

Define: no results? What exact output are you getting and if you are getting a blank page what does a 'view source' in your browser show?

randal_138 · January 12, 2011

It shows my html coding, but the PHP is gone and not querying the database.

Here is the code that works, but does not incorporate the $i listing element:

<?php
$subject = $_POST['subject'];
$message_text = $_POST['message_text'];

//Connect to mysql server
$link = mysql_connect('localhost', 'ryan_ryan', 'nealon');
if(!$link)
{
die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db('ryan_iframe');
if(!$db)
{
die("Unable to select database");
}

// validate incoming values
$subject = (isset($_GET['subject'])) ? (int)$_GET['subject'] : 0;
$message_text = (isset($_GET['message_text'])) ? (int)$_GET['message_text'] : 0;

ob_start();

$query = sprintf('
SELECT
SUBJECT, MSG_TEXT, UNIX_TIMESTAMP(MSG_DATE) AS MSG_DATE
FROM
FORUM_MESSAGE
WHERE
SUBJECT = %d OR
MSG_TEXT = %d
ORDER BY
MSG_DATE DESC',
DB_DATABASE,
DB_DATABASE,
$subject,
$subject);

$result = mysql_query($query) or die(mysql_error());
$num=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result))
{
echo '<div style="width: 400px;padding:20px;">';
echo '<table border=0 width="400px">';
echo '<tr>';
echo '<td style="vertical-align:top;width:auto;">';
echo 'Date: ';
echo '</td>';
echo '<td style="vertical-align:top;width:320px;">';
echo date('F d, Y', $row['MSG_DATE']) . '</td>';
echo '</tr>';
echo '<tr>';
echo '<td style="vertical-align:top;width:auto;">';
echo 'Subject: ';
echo '</td>';
echo '<td style="vertical-align:top;width:320px;">';
echo '<div>' . htmlspecialchars($row['SUBJECT']) . '</div>';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td style="vertical-align:top;width:auto;">';
echo 'Message: ';
echo '</td>';
echo '<td style="vertical-align:top;width:320px;">';
echo '<div>' . htmlspecialchars($row['MSG_TEXT']) . '</div>';
echo '</td>';
echo '</tr>';
echo '<tr>';
echo '<td style="vertical-align:top;width:auto;">';
echo '</td>';
echo '<td style="vertical-align:top;width:320px;text-align:center;">';
echo '<form method="post">';
echo '<input type="hidden" name="update" value="true" />';
echo '<input type="submit" value="Update" />';
echo '         ';
echo '<input type="hidden" name="delete" value="true" />';
echo '<input type="submit" value="Delete" />';
echo '</form>';
echo '</td>';
echo '</tr>';
echo '</table>';
echo '<br />';
echo '<hr />';
echo '</div>';
}
mysql_free_result($result);

?>

When I incorporate the $i = 0 and other script to create rows/lists, it no longer queries the database. Check my initial post for the scripting that does not work.

Thanks again!

Ryan

Pikachu2000 · January 12, 2011

Are $subject and $message_text both expected to contain numeric values?

randal_138 · January 14, 2011

So what your saying is that I can only do one? The problem is that I want to pull up the message_text associated with the subject and modify that. Can't I just assign numerical values to rows with rows containing the subject, message_text, delete button and edit button? That way I can delete a post with one button and query the DB with the other post so that I can use the UPDATE script?

Hope I explained this so that you understand.

Thanks again.

Ryan

Pikachu2000 · January 14, 2011

No, that isn't what I'm saying. Following the logic in your code, these lines will take the incoming form values and assign the values to the $subject and $message_text variables.

$subject = $_POST['subject'];
$message_text = $_POST['message_text'];

Then further along in the code, these lines override that assignment, and assign the the variables either the integer value of $_GET['subject'] and $_GET['message_text'] OR zero.

$subject = (isset($_GET['subject'])) ? (int)$_GET['subject'] : 0;
$message_text = (isset($_GET['message_text'])) ? (int)$_GET['message_text'] : 0;

So, the reason I asked if both $subject and $message_text are expected to be numeric is that in the script, they will be numeric, even though by the names $subject and $message_text they sound as though they should be strings.

randal_138 · January 16, 2011

If what you say is true, which I believe, then how exactly would I make the string look? Outside of that, I should be good to go on finishing the script and testing it.

Ryan

Pikachu2000 · January 16, 2011

Can you provide an example of what actual data you'd expect to be in the `SUBJECT`and `MSG_TEXT` fields in the database?

randal_138 · January 16, 2011

Here is my basic table set-up:

table name: FORUM_MESSAGE

SUBJECT, varchar(100), input box

MSG_TEXT, varchar(500), textfield

MSG_DATE, timestamp

Ryan

Muddy_Funster · January 16, 2011

Wouldn't it be a whole lot easier (and better practice) just to have an ID field in the database table that you can then use to index the records?

Pikachu2000 · January 16, 2011

Yup. That's where I was going with this. You need to consider adding an index field to that table. Unsigned INT, autoincrement, NOT NULL, primary key. Then it will be much easier to accomplish what you're wanting to do.

randal_138 · January 17, 2011

You guys are so smart. Will give that a whirl and post a reply if I am still having issues.

Ryan

randal_138 · January 18, 2011

There is progess. I had some nice syntax errors that I had to fix in order for the results to display. The $i function is working, but now I am having some more coding errors. Easier stuff this time. I can't delete the row from the table. The code is very short and is as follows:

<?php

if($_POST['delete']) // from button name="delete"
{
for($i=0;$i<$count;$i++)
{
	$del_id = $i;
	$sql = "DELETE FROM FORUM_MESSAGE WHERE id='$del_id'";
	$result = mysql_query($sql) or die (mysql_error());
}

if($result)
{
	header('Location: message-deleted.php');
}
}

?>

Thanks again!

Ryan

Sign In

$query returning no database results

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information