Pulling data and printing it in an HTML table

Question

Hello,

From a MySQL table called "submission" containing the fields "loginid, submissionid, title, url, datesubmitted, displayurl", I would like to print an HTML table thats contains all "title" and corresponding "datesubmitted" where "loginid" equals "$profile." The code I am trying to use is below. It isn't working. Any ideas why it isn't working?

Thanks in advance,

John

$profile = $_GET['profile'];  

$sqlStr = "SELECT loginid, submissionid, title, url, datesubmitted, displayurl
             FROM submission
            WHERE loginid = $profile
         ORDER BY datesubmitted DESC";    

$result = mysql_query($sqlStr);

$arr = array(); 
echo "<table class=\"samplesrec\">";
while ($row = mysql_fetch_array($result)) { 
    echo '<tr>';
    echo '<td class="sitename1"><a href="http://www.'.$row["url"].'"&gt;'.$row["title"].'&lt;/a&gt;&lt;/td&gt;';
    echo '</tr>';
    echo '<tr>';
    echo '<td class="sitename2">'.$row["datesubmitted"].'</a></td>';
    echo '</tr>';
    }
echo "</table>";

Answer 1

Your query is probably failing.

Try echoing the return from mysql_error(); after trying the query to see what the issue might be.

You should also protect your input against injection. If loginID is a username, you need to surround a string in a mySQL query with quotes - if loginID is a username. If it's an integer you may be okay.

There are more robust ways to do this but simply:

  $profile = mysql_real_escape_string($_GET['profile']);

  $sqlStr = "SELECT loginid, submissionid, title, url, datesubmitted, displayurl
               FROM submission
              WHERE loginid = '$profile'
           ORDER BY datesubmitted DESC";

  $result = mysql_query($sqlStr);

  if($result) {
      // Handle output
  } 
  else {
      echo 'query failed';
      // don't leave this here in production!
      echo mysql_error();
  }

Answer 2

One problem I can see is you are not checking in the return value of mysql_query()

mysql_query() returns false if it fails to execute the query. So you need to do a check, something like:

$result = mysql_query($sqlStr);
if(! $result) {

  //....error occured...prepare $message
  die($message);
}

Answer 3

your question regards to debugging, the most important programming art. Noone can find an error for you, you have to do it yourself. With help of little tricks.

change $profile = $_GET['profile']; to $profile = intval($_GET['profile'];)

change $result = mysql_query($sqlStr); to

$result = mysql_query($sqlStr) or trigger_error(mysql_error()." in ".$sqlStr);

andd following 2 lines at the top of your code, run it again and see what it say. if still nothing, you don't have matching records in your table.

ini_set('display_errors',1);
error_reporting(E_ALL);