PHP with MySQL return query 1

[LuckySyringe]

I need to limit my rows as well as print out the data inversely, so the most recent additions come out on top and there is only six results on one page. This is what I have so far:

<?php
$start = $_GET['start'];
$offset = $_GET['offset'];
if (!$start || !$offset) {
	$start = 0;
	$offset = 6;}
$end = $start + $offset;
$dbHost = 'localhost';
$dbUser = 'tempuser';
$dbPass = 'temppass';
$dbDatabase = 'tempdatabase';
$dbTable = 'temptable';

$dbConnection = @mysql_connect($dbHost, $dbUser, $dbPass) or die("Could not connect: ".mysql_error()."<br />");
				@mysql_select_db("$dbDatabase") or die("Could not select database.<br />");
//  I want this so that the start number and the offset are in the url
//  and a link under the printed results with new start and offset numbers
$query = "SELECT * FROM ".$dbTable." LIMIT ".$start.",".$offset;
$result = @mysql_query($query) or die ('Query failed: '.mysql_error().'<br />');
$num_results = @mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++) {
	$row = @mysql_fetch_assoc($result);
	echo "\t<p class=\"item\">\n".
		 "\t\t<a href=\"".$dbTable."/item.php?id=".stripslashes($row['id'])."\">".stripslashes($row['id'])."</a>\n".
		 "\t\t<span class=\"item-title\"><a href=\"".$dbTable."/item.php?id=".stripslashes($row['id'])."\">".htmlspecialchars(stripslashes($row['title']))."</a></span>".
		 "\t\t<span class=\"item-info\">".stripslashes($row['description'])."</span>".
		 "\t</p>\n";}
/*
$start = $i + 1;
echo "<p>".$i." - ".$end." of ".$num_results." "; // I realize this is redundant
//  This is where the previous / next page will be printed
switch ($i) {
	case $i < 8;
		echo "<a href=\"".$_SERVER['PHP_SELF']."?page=list&start=".$start."&offset=".$offset."\">Next Page</a>";
		break;
	case $i > 8;
		$prev = $start - $offset;
		echo "<a href=\"".$_SERVER['PHP_SELF']."?page=list&start=".$prev."&offset=".$offset."\">Previous Page</a> <a href=\"".$_SERVER['PHP_SELF']."?page=list&start=".$start."&offset=".$offset."\">Next Page</a>";
		break;
}
echo "</p>";
*/
@mysql_free_result($result);
@mysql_close($dbConnection);
?>

Any help would be greatly appreciated.

 

[KarveR]

Which piece do you need help with ?

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[jpadie]

assuming you use an autoincrement called id as your primary key try your query as:

$query = "SELECT * 
          FROM $dbTable
          ORDER BY `id` DESC 
          LIMIT $start, $offset";

i.e. simply use order by to ensure the recordset is returned in the right order and use the limit element to limit the number of records per page (as you are doing) with an appropriate offset (which will work against the recordset order).

 

[LuckySyringe]

Thank you jpadie. I guess I didn't read into it as far as I should, and I apologize for not being descriptive enough.

 

[jpadie]

??, I thought you were clear enough!

one other thing. consider using

while($row = mysql_fetch_assoc($query))
{
//stuff here
}

instead of your for-next loop.

I also have to say that i am not a fan of suppressing errors using the '@' sign. amongst other things, it makes debugging more difficult than needs be !

 

[LuckySyringe]

Thanks for the tip, I'll try the while loop now.
Also, I'm using the @ supressor because I started getting

[18-Dec-2005 19:07:50] PHP Warning: mysql_close(): supplied argument is not a valid MySQL-Link resource in C:\

http://www\temp-mysql.php

on line 37

...and...

[18-Dec-2005 19:09:05] PHP Warning: Wrong parameter count for mysql_result() in C:\

http://www\temp-mysql.php

on line 36

...in my php error log, using (almost) exactly what I posted above. I messed around, found no cure, so I just supressed them all.

 

[LuckySyringe]

haha speak of the devil, when I tried using the while loop you gave me, my error log returns

[19-Dec-2005 16:44:20] PHP Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\

http://www\temp-mysql.php

on line 22

 

[jpadie]

then the sql query is not executing properly. there is an error in the syntax or in the connection functions etc.

try adding "error_reporting (E_ALL);" to the start of the script and removing all the ampersats (@s) to identify the errors.

 

[LuckySyringe]

Hmm, for some odd reason adding error_reporting(E_ALL) has no effect on the document, it loads normally, but the errors and warnings still show up in my error log. Also, to my understanding, either way I choose to display the errors I should still get the same error message.
And an off-topic MySQL question, is it possible to select all rows from all tables and print only the most recent additions
ie:

SELECT * FROM * ORDER BY `added` DESC LIMIT 0,6

where "FROM *" would change to the effect of all tables, and `added` is a timestamp (if that makes any difference).

 

[jpadie]

you may be right on the error reporting- i have always wanted the errors to appear so have never experimented with line by line suppression. the point remains, however, that there is something wrong either in your connect, select_db or query. those are the reasons why you don't get back a valid result handle.

to your second question: no it is not possible to return rows from all tables like that. feasibly it is possible to construct a relationship between tables that might allow you to return all the rows but it would end up being a complex join. would be better to use php to analyse the database structure and iteratively retrieve the last few records of each table it comes across. can't do this in one line but it's not v complex.

 

[LuckySyringe]

Thank you again, I'll look into my code for the source of the errors, and I'll check out both options later.

 

[jpadie]

something like this:

//db connect

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql) or die (mysql_error());
while ($row = mysql_fetch_row($result)):   
  $tablename = $row[0];
  $sql2 = "Select * from $tablename order by `dateadded` DESC limit 6";
  $result2 = mysql_query($sql2) or die (mysql_error());
  while ($row2 = mysql_fetch_assoc($result2)):
   echo "<br/><div style=\"bgcolor:lightblue;\">";
   echo "Last 6 records from table: $tablename<br/><pre>";
   print_r ($row2);
   echo "</pre></div><br/>";
  endwhile;
endwhile;
?>

 

[LuckySyringe]

Thank you again, youre too kind

-> LuckySyringe

 

[LuckySyringe]

I ran into a problem...
I'm trying to print out the last six of the most recent additions of all tables, but with that code (slightly modified) I get the last 6 from each table. I apologize if I did not made it clear in the beginning.

<?php
include 'include/dbconnect.php';
dbconnect();

$query = "SHOW TABLES FROM ".$dbDatabase;
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_row($result)) {
	$tableName = $row[0];
	$query_2 = "SELECT * FROM $tableName ORDER BY `added`";
	$result_2 = mysql_query($query_2) or die (mysql_error());
	while ($row_2 = mysql_fetch_assoc($result_2)) {
		echo "\t<p class=\"item\">\n".
			 "\t\t<a href=\"".$tableName."/item.php?id=".stripslashes($row_2['id'])."\"><img src=\"".$tableName."/thumbs/".stripslashes($row_2['id']).".gif\" class=\"item-thumb\" /></a>\n".
			 "\t\t<span class=\"item-type\">".$tableName."</span>".
			 "\t\t<span class=\"item-title\"><a href=\"".$tableName."/item.php?id=".stripslashes($row_2['id'])."\">".htmlspecialchars(stripslashes($row_2['title']))."</a></span>".
			 "\t\t<span class=\"item-info\">".stripslashes($row_2['description'])."</span>".
			 "\t\t<br class=\"item-clear\" />".
			 "\t</p>";
	}
}

@mysql_free_result($result);
@mysql_free_result($result_2);
@mysql_close($dbConnection);
?>

I tried messing around by replacing the 2nd while loop with a for loop, and adding a for loop after the 2nd while loop, but my experimentations didn't work.

-> LuckySyringe

 

[jpadie]

a bit more programming needed.
you need to store all the records in an array, sort the array and then bring out the last six.

<?
while ($row = mysql_fetch_row($result)) :
    $tableName = $row[0];
    $query_2 = "SELECT * FROM $tableName ORDER BY `added` limit 6";
    $result_2 = mysql_query($query_2) or die (mysql_error());
    while ($row_2 = mysql_fetch_assoc($result_2)):
			$row_2['tablename'] = $tablename;
			$array[$row_2['added'.microtime()] = $row_2;
	endwhile;
endwhile;

krsort ($array);

$i = count($array) > 6 ? 6 : count($array);
$cnt = 0;
foreach ($array as $key=>$val) :
		if ($cnt < $i ) :
		
        echo "\t<p class=\"item\">\n".
             "\t\t<a href=\"".$val['tableName']."/item.php?id=".stripslashes($val['id'])."\"><img src=\"".$val['tableName']."/thumbs/".stripslashes($val['id']).".gif\" class=\"item-thumb\" /></a>\n".
             "\t\t<span class=\"item-type\">".$val['tableName']."</span>".
             "\t\t<span class=\"item-title\"><a href=\"".$val['tableName']."/item.php?id=".stripslashes($val['id'])."\">".htmlspecialchars(stripslashes($val['title']))."</a></span>".
             "\t\t<span class=\"item-info\">".stripslashes($val['description'])."</span>".
             "\t\t<br class=\"item-clear\" />".
             "\t</p>";
		$cnt++;
		else:
			exit;
		endif;
endforeach;
?>

 

[LuckySyringe]

I tried your code, but it only returns one line and when I check my error log, I have 13 lines of

[20-Dec-2005 18:28:28] PHP Notice: Undefined index: added0.42585300 1135121308 in C:\

http://www\home.page

on line 18

where the numbers in blue are different by each line.

And last night I tried adding the rows to an array and this is how far I got...

$cItem = 0;

$query = "SHOW TABLES FROM ".$dbDatabase;
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_row($result)) {
	$tableName = $row[0];
	$query_2 = "SELECT * FROM $tableName ORDER BY `added`";
	$result_2 = mysql_query($query_2) or die (mysql_error());
	while ($row_2 = mysql_fetch_assoc($result_2)) {
		$newArray[$cItem] = array(
			"\t\t\t<p class=\"item\">\n".
			"\t\t\t\t<a href=\"".$tableName."/item.php?id=".stripslashes($row_2['id'])."\"><img src=\"".$tableName."/thumbs/".stripslashes($row_2['id']).".gif\" class=\"item-thumb\" /></a>\n".
			"\t\t\t\t<span class=\"item-type\">".$tableName."</span>\n".
			"\t\t\t\t<span class=\"item-title\"><a href=\"".$tableName."/item.php?id=".stripslashes($row_2['id'])."\">".htmlspecialchars(stripslashes($row_2['title']))."</a></span>\n".
			"\t\t\t\t<span class=\"item-info\">".stripslashes($row_2['description'])."</span>\n".
			"\t\t\t\t<br class=\"item-clear\" />\n".
			"\t\t\t</p>\n");
		$cItem++;
	}// while #2
	echo count($newArray);
	if (count($newArray) > 0) {
		for ($k = 0; $k <= $i; $k++) {
			echo $newArray[$k];
		}// for
	}// if
}// while #1

Now the problem with this one is that it prints

10ArrayArrayArrayArrayArrayArrayArray18ArrayArrayArrayArrayArrayArrayArray19ArrayArrayArrayArrayArrayArrayArray

on the page, and six lines of

[20-Dec-2005 18:06:03] PHP Notice: Undefined offset: 1 in C:\

http://www\home.page

on line 31

in my error log, where the number in blue is 1-6.

I confused myself at this point, I guess I made it too complicated. I don't have much experience with arrays.

-> LuckySyringe

 

[jpadie]

some thoughts:
* $query_2 needs to be ordere by added desc and not by added,
* the microtime function is messing your array keys as it contain a decimal point. instead use

$array["added_".uniqid()]= $row[2];

* the reason for adding the uniqid is just in case there were two database writes with the same value for added. if you are certain that there are not, eliminate the uniqid element and just go for $array["added"] = $row[2].

*in the add to the $cItem element - why are you adding it as an array? lose the array declaration and the opening and closing brackets and see whether it makes a difference.
lastly - i don't think your code does what you had asked - ie retrieve the last six rows added across all the tables in the database. my code *should* do (i have no data to test against on the current pc) if you change the array declaration to that set out above.

 

[jpadie]

sorry - more data may be needed:

the array structure i proposed is important in order to get the cross-table order right. the krsort function orders the second array by reference to its keys (in reverse). the keys are the added field (should a either a unix timestamp or a mysql datetime stamp, of course). ordering in reverse causes the highest (latest) to be first.

 

[LuckySyringe]

alright, so far from what you gave me, I have this:

// database connect
$query = "SHOW TABLES FROM ".$dbDatabase;
$result = mysql_query($query) or die (mysql_error());
while ($row = mysql_fetch_row($result)) {
    $tableName = $row[0];
    $query_2 = "SELECT * FROM $tableName ORDER BY `added` DESC LIMIT 6";
    $result_2 = mysql_query($query_2) or die (mysql_error());
    while ($row_2 = mysql_fetch_assoc($result_2)){
            $row_2['tableName'] = $tableName;
            $array["added_".uniqid()]= $row[2];[COLOR=green]// line 16[/color]
    }
}

krsort ($array);

$i = count($array) > 6 ? 6 : count($array);
$cnt = 0;
foreach ($array as $key=>$val){
	if ($cnt < $i ) {
		echo "\t<div class=\"item\">\n".
			"\t<b class=\"b1\"></b><b class=\"b2\"></b><b class=\"b3\"></b><b class=\"b4\"></b>\n".
			"\t\t<div class=\"boxcontent\">\n".
			"\t\t\t<p>\n".
			"\t\t\t\t<a href=\"".$tableName."/item.php?id=".stripslashes($row_2['id'])."\"><img src=\"".$tableName."/thumbs/".stripslashes($row_2['id']).".gif\" class=\"item-thumb\" /></a>\n".
			"\t\t\t\t<span class=\"item-type\">".$tableName."</span>\n".
			"\t\t\t\t<span class=\"item-title\"><a href=\"".$tableName."/item.php?id=".stripslashes($row_2['id'])."\">".htmlspecialchars(stripslashes($row_2['title']))."</a></span>\n".
			"\t\t\t\t<span class=\"item-info\">".stripslashes($row_2['description'])."</span>\n".
			"\t\t\t\t<br class=\"item-clear\" />\n".
			"\t\t\t</p>\n".
			"\t\t</div>\n".
			"\t<b class=\"b4b\"></b><b class=\"b3b\"></b><b class=\"b2b\"></b><b class=\"b1b\"></b>\n".
			"\t</div>\n\n";
		$cnt++;}
	else{
		exit;}
}
//database free results and disconnect

the problem is that it returns six rows, but they are all blank.
ie:

<a href="items/item.php?id="><img src="items/thumbs/.gif" class="item-thumb" /></a>

it should be like

<a href="items/item.php?id=5"><img src="items/thumbs/5.gif" class="item-thumb" /></a>

and my error log shows

[20-Dec-2005 19:27:49] PHP Notice: Undefined offset: 2 in C:\

http://www\home.page

on line 16

six times. (line 16 is commented in green above.)
Also I'm not familiar with some of your syntax, and I'm curious - whats going on here

$i = count($array) > 6 ? 6 : count($array);

how do the question mark and colon come to play?

-> LuckySyringe

 

[jpadie]

the tablename will come out blank because you are not referencing the variables by its array element

$val['tableName']

further (and this is the real problem) - each time in the echo statements that you use $row_2 you should be using the $val variable instead.

to get a sense for what is happening try inserting

print_r ($val);

just inside the foreach loop. essentially the code picks up the large variable row_2 and takes six steps through it, each step it gives you the key (the date + uniqid) and the value. the value, in this case, is itself an array that represents the database row.

lastly the ? : is the "ternary" operator => CRITERIA ? IF TRUE : IF FALSE. It could equally well be done with if then else statements but this way is a little more elegant code-wise.