Not Showing All Results with mySQL

Anon

I've recently made a PHP script for a website that I've been working on for quite a while.

This script accesses an SQL database using mySQL. My problem is that the scripts don't seem to be showing all the results. For example, I'll have a query like this for the database:

SELECT * FROM $accessTbl WHERE filetype='Movies'

After which I get it to display the results. My major problem is that it doesn't seem to show all the results. For example if I add a file with a "filetype" of 'Movies' it wont show up in the results until I add another file with a "filetype" of 'Movies' and then that recently added one won't show up until I add another. It just continues in this loop.

I don't know if I explained it that well, but any help would be greatly appreciated.

[deleted]

Can you give us the code you use to extract and print the data?

Anon

Here's one example:

$DBquery = "SELECT * FROM $accessTbl WHERE id > 0 ORDER BY filename

if (!($link=mysql_pconnect($accessHst, $accessUsr, $accessPss))) {
die("Error connecting to $accessHost by $accessUsr"); }

if (!mysql_select_db($accessDB, $link)) {
	die("Error selecting database $accessDB"); }

if (!($result=mysql_query($DBquery, $link))) {
	die("Error completing $DBquery statement"); }

if (!($id2=mysql_fetch_object($result))) {
	die("Error in selecting entry. That entry is non-existent"); }

while($id2=mysql_fetch_object($result)) {
$fid = $id2->id; $ffilename = $id2->filename; $fauthorname = $id2->authorname; $fauthorlink = $id2->authorlink; $ffilesize = $id2->filesize; $fdateadded = $id2->dateadded;
echo("<a href=viewfile.php?id=$fid>$ffilename</a> By <a href=mailto:$fauthorlink>$fauthorname</a>. $fdateadded . Size: $ffilesize k "); }

[deleted]

You'll want to start by reading this:
http://www.xs4all.nl/~vinny/index.html#4

Then have a good read in the PHP manual to see just what mysql_fetch_object() really does.

kparker

Vincent, your FAQ #4 is a great intro. I just have one suggest of an alternate coding that reduces all the nesting and moves the error messages right next to the conditions that trigger them:

$sql = "SELECT * FROM table";

// Step 1: connect

if (! ($conn_id = mysql_connect($host,$user,$pwd)))
  echo 'Could not connect to database server';

// Step 2: select database

elseif ( ! mysql_select_db($database, $conn_id))
  echo 'Could not select database';

// Step 3: issue query

elseif ( ! ($result = mysql_query($sql, $conn_id)))
  {
  // Step 3 failed, non-fatal
  // (might want to run other queries after this)
  echo 'SQL query failed:'.mysql_error()." ".$sql;
  }

// Step 4: Find out if any results
// (NOTE: this is only appropriate for SELECT queries;
// INSERT, UPDATE, and DELETE need mysql_affected_rows()
// and of course you don't fetch anything afterward.)

else if ( mysql_num_rows($result) < 1 )
  {
  // Step 4 failed, non-fatal, No results found
  echo 'Your query did not return any results';
  }

// Step 5
// Yay, results found, process them
else
  {
  while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
    print_r($row);
    }
  }

kparker

BTW, I substituted shorter variable names here, not because I thought there was anything wrong with your longer original ones, but so that the code here wouldn't wrap.

[deleted]

Thanks Kirk!

You input is much appreciated.
I'll put your version in too just to show the deferent methods and let the end-user decide which is better for him/her.

If you have any other things you'd like to add, please write an articlet and let me know and I'll include it!

kparker

All I'm lacking is time; the inclination is certainly there. You got any spare time you could pass on to me? :-)

[deleted]

spare time? what's that? :-)

kparker

Nevermind; it's something I heard of once but I now realize the person didn't know what he was talking about!