...

View Full Version : First Row Missing!



fr600
08-03-2008, 12:45 AM
OK, I've a weird situation here. I've created a complete package of my problem.

This is the database:

CREATE TABLE IF NOT EXISTS categories (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO categories (id, name) VALUES
(1, 'Porsche'),
(2, 'Ferrari'),
(3, 'Mercedes');

CREATE TABLE IF NOT EXISTS items (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(64) NOT NULL DEFAULT '',
category int(10) unsigned NOT NULL DEFAULT '0',
added_date date NOT NULL DEFAULT '0000-00-00',
owner int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY owner (owner),
KEY name (name),
KEY added_date (added_date)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO items (id, name, category, added_date, owner) VALUES
(1, 'Boxter', 1, '2008-07-28', 1),
(2, '210 E-Class', 3, '2008-07-28', 3),
(3, 'Enzo', 2, '2008-07-28', 1),
(4, '575M', 2, '2008-07-29', 2),
(5, '208 CLK', 3, '2008-07-29', 2),
(6, '944 Cabriolet', 1, '2008-07-30', 3),
(7, '944 Coupe', 1, '2008-07-30', 2),
(8, '220 S-Class', 3, '2008-07-30', 2),
(9, '456M', 2, '2008-07-30', 3),
(10, 'F50', 2, '2008-07-31', 1),
(11, '210 Wagon', 3, '2008-07-31', 1),
(12, '911 Turbo', 1, '2008-07-31', 2),
(13, '201 Sedans', 3, '2008-08-01', 1),
(14, '575M', 2, '2008-08-01', 2),
(15, '612 Scaglietti', 2, '2008-08-02', 3),
(16, '356C Coupe', 1, '2008-08-02', 2);

CREATE TABLE IF NOT EXISTS users (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
username varchar(14) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE KEY username (username)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO users (id, username) VALUES
(1, 'admin'),
(2, 'test'),
(3, 'engineer');

and this is the php file:

<?php
$mysql_hostname = "localhost";
$mysql_username = "root";
$mysql_password = "pass";
$mysql_database = "test";

@mysql_connect($mysql_hostname, $mysql_username, $mysql_password);
mysql_select_db($mysql_database);

function categorylist()
{
$ret = array();
$res = mysql_query("SELECT id, name FROM categories ORDER BY id");
while ($row = mysql_fetch_array($res))
$ret[] = $row;
return $ret;
}

function itemstable($res)
{
echo "<table cellpadding=0 cellspacing=0>";
echo "<tr>";
echo "<td width=50>ID</td>";
echo "<td width=100>Category</td>";
echo "<td width=150>Name</td>";
echo "<td width=150>Added Date</td>";
echo "<td width=150>Owner</td>";
echo "</tr>";
while ($row = mysql_fetch_assoc($res))
{
echo "<tr>";
echo "<td>" . $row['iid'] . "</td>";
echo "<td>" . $row['catname'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['added_date'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "</tr>";
}
echo "</table>";
echo "<br>";
}

$cats = categorylist();
$display = "";
foreach ($cats as $rowc)
{
$res = mysql_query("
SELECT items.id as iid, items.*, items.added_date, categories.id, categories.name as catname, users.id, users.username FROM items
LEFT JOIN categories ON category = categories.id
LEFT JOIN users ON items.owner = users.id
WHERE added_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND category = " . $rowc['id'] . "
ORDER BY iid DESC");
while($row = mysql_fetch_array($res))
$display .= itemstable($res);
}
echo "$display";
?>

...and the problem is you'll see is that the newest item from each category is missing. I know I doing something wrong here and I'm wondering if you could help me out.

PS. I'm using mysql 5.1.25, php 5.2.6, and apache 2.2.9.

oesxyl
08-03-2008, 12:57 AM
in itemstable function you use $row['iid'], $row['catname'], $row['name'], $row['added_date'], $row['username']


function itemstable($res){
echo "<table cellpadding=0 cellspacing=0>";
echo "<tr>";
echo "<td width=50>ID</td>";
echo "<td width=100>Category</td>";
echo "<td width=150>Name</td>";
echo "<td width=150>Added Date</td>";
echo "<td width=150>Owner</td>";
echo "</tr>";
while($row = mysql_fetch_assoc($res)){
echo "<tr>";
echo "<td>" . $row['iid'] . "</td>";
echo "<td>" . $row['catname'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['added_date'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "</tr>";
}
echo "</table>";
echo "<br>";
}


but in your query use alias:


$res = mysql_query("
SELECT items.id as iid, items.*, items.added_date, categories.id, categories.name as catname, users.id, users.username FROM items
LEFT JOIN categories ON category = categories.id
LEFT JOIN users ON items.owner = users.id
WHERE added_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND category = " . $rowc['id'] . "
ORDER BY iid DESC");


regards

fr600
08-03-2008, 01:07 AM
Well sir, I'm not an expert like you guys, could you please tell me how I can fix this?

Thanks

oesxyl
08-03-2008, 01:21 AM
try this way:

function itemstable($res){
echo "<table cellpadding=0 cellspacing=0>";
echo "<tr>";
echo "<td width=50>ID</td>";
echo "<td width=100>Category</td>";
echo "<td width=150>Name</td>";
echo "<td width=150>Added Date</td>";
echo "<td width=150>Owner</td>";
echo "</tr>";
while($row = mysql_fetch_assoc($res)){
echo "<tr>";
echo "<td>" . $row['iid'] . "</td>";
echo "<td>" . $row['catname'] . "</td>";
echo "<td>" . $row['items.name'] . "</td>";
echo "<td>" . $row['items.added_date'] . "</td>";
echo "<td>" . $row['items.username'] . "</td>";
echo "</tr>";
}
echo "</table>";
echo "<br>";
}

if I mess something, just look in your query and see what is the correct name and change it.

messed, :)
iid and catname must remain without items. Is correct now, I hope
regards

fr600
08-03-2008, 01:42 AM
items.name, items.added_date, and items.username aren't producing any result; they're blank. :(

and username is from users table, in items it listed as owner connected users.id and I could make you understand :S

oesxyl
08-03-2008, 01:53 AM
add a print_r in while and check the names returned as keys in $row and the values what would be displayed.


$cats = categorylist();
$display = "";
foreach ($cats as $rowc)
{
$res = mysql_query("
SELECT items.id as iid, items.*, items.added_date, categories.id, categories.name as catname, users.id, users.username FROM items
LEFT JOIN categories ON category = categories.id
LEFT JOIN users ON items.owner = users.id
WHERE added_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND category = " . $rowc['id'] . "
ORDER BY iid DESC");
while($row = mysql_fetch_array($res)){
print_r($row);
$display .= itemstable($res);
}
}
echo "$display";
?>

regards

fr600
08-03-2008, 02:11 AM
Yes I got a bunch of keys like:


Array ( [0] => 16 [iid] => 16 [1] => 16 [id] => 2 [2] => 356C Coupe [name] => 356C Coupe [3] => 1 [category] => 1 [4] => 2008-08-02 [added_date] => 2008-08-02 [5] => 2 [owner] => 2 [6] => 2008-08-02 [7] => 1 [8] => Porsche [catname] => Porsche [9] => 2 [10] => test [username] => test )


Array ( [0] => 15 [iid] => 15 [1] => 15 [id] => 3 [2] => 612 Scaglietti [name] => 612 Scaglietti [3] => 2 [category] => 2 [4] => 2008-08-02 [added_date] => 2008-08-02 [5] => 3 [owner] => 3 [6] => 2008-08-02 [7] => 2 [8] => Ferrari [catname] => Ferrari [9] => 3 [10] => engineer [username] => engineer )


Array ( [0] => 13 [iid] => 13 [1] => 13 [id] => 1 [2] => 201 Sedans [name] => 201 Sedans [3] => 3 [category] => 3 [4] => 2008-08-01 [added_date] => 2008-08-01 [5] => 1 [owner] => 1 [6] => 2008-08-01 [7] => 3 [8] => Mercedes [catname] => Mercedes [9] => 1 [10] => admin [username] => admin )


No idea what to do now.

Ok, you've got the whole thing and you know what I want and the problem I'm having.

Is it too much if I ask you to rewrite the codes as it would produce the desired outcome?

oesxyl
08-03-2008, 02:24 AM
Yes I got a bunch of keys like:


Array ( [0] => 16 [iid] => 16 [1] => 16 [id] => 2 [2] => 356C Coupe [name] => 356C Coupe [3] => 1 [category] => 1 [4] => 2008-08-02 [added_date] => 2008-08-02 [5] => 2 [owner] => 2 [6] => 2008-08-02 [7] => 1 [8] => Porsche [catname] => Porsche [9] => 2 [10] => test [username] => test )


Array ( [0] => 15 [iid] => 15 [1] => 15 [id] => 3 [2] => 612 Scaglietti [name] => 612 Scaglietti [3] => 2 [category] => 2 [4] => 2008-08-02 [added_date] => 2008-08-02 [5] => 3 [owner] => 3 [6] => 2008-08-02 [7] => 2 [8] => Ferrari [catname] => Ferrari [9] => 3 [10] => engineer [username] => engineer )


Array ( [0] => 13 [iid] => 13 [1] => 13 [id] => 1 [2] => 201 Sedans [name] => 201 Sedans [3] => 3 [category] => 3 [4] => 2008-08-01 [added_date] => 2008-08-01 [5] => 1 [owner] => 1 [6] => 2008-08-01 [7] => 3 [8] => Mercedes [catname] => Mercedes [9] => 1 [10] => admin [username] => admin )

No idea what to do now.

Ok, you've got the whole thing and you know what I want and the problem I'm having.

Is it too much if I ask you to rewrite the codes as it would produce the desired outcome?
I was wrong, the problem is in that function but not the field names. :)

ok, let's try to fix:

<?php
$mysql_hostname = "localhost";
$mysql_username = "root";
$mysql_password = "pass";
$mysql_database = "test";

@mysql_connect($mysql_hostname, $mysql_username, $mysql_password);
mysql_select_db($mysql_database);

function categorylist()
{
$ret = array();
$res = mysql_query("SELECT id, name FROM categories ORDER BY id");
while ($row = mysql_fetch_array($res))
$ret[] = $row;
return $ret;
}

$cats = categorylist();
$display = "";
foreach ($cats as $rowc)
{
$res = mysql_query("
SELECT items.id as iid, items.*, items.added_date, categories.id, categories.name as catname, users.id, users.username FROM items
LEFT JOIN categories ON category = categories.id
LEFT JOIN users ON items.owner = users.id
WHERE added_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND category = " . $rowc['id'] . "
ORDER BY iid DESC");
$display .= "<table cellpadding=0 cellspacing=0>
<tr>
<td width=50>ID</td>
<td width=100>Category</td>
<td width=150>Name</td>
<td width=150>Added Date</td>
<td width=150>Owner</td>
</tr>";
while($row = mysql_fetch_array($res)){
$display .= "<tr>
<td>" . $row['iid'] . "</td>
<td>" . $row['catname'] . "</td>
<td>" . $row['name'] . "</td>
<td>" . $row['added_date'] . "</td>
<td>" . $row['username'] . "</td>
</tr>";
}
$display .= "</table>
<br>";
}
echo $display;
?>

regards

fr600
08-03-2008, 02:40 AM
OK cool, this one works. But is it somehow possible to do this without altering the function (it's really a huge function, I just showed a simplified version to you) because many files depends on that function?

PS. I really appreciate you helping a stranger for no reason.

oesxyl
08-03-2008, 02:55 AM
OK cool, this one works. But is it somehow possible to do this without altering the function (it's really a huge function, I just showed a simplified version to you) because many files depends on that function?
you can put the function back in the code if you need it in other files, but you can't use it in that foreach without changes because:
- echo the content of $row, not append to $display as you need
- use a mysql_fech_asssoc inside who don't fit here
- output the header and footer of the table, don't fit here too

[/QUOTE]PS. I really appreciate you helping a stranger for no reason.[/QUOTE]
who said that I have no reason? :)

regards

fr600
08-03-2008, 03:02 AM
Ahh I see, thanks a lot, really!!

What's the reason btw? :D

Nevermind, just kidding..

fr600
08-03-2008, 04:04 AM
Sorry, one last thing. Let's say, when one category is blank (with no record within the interval date), the following still shows up.


$display .= "<table cellpadding=0 cellspacing=0>
<tr>
<td width=50>ID</td>
<td width=100>Category</td>
<td width=150>Name</td>
<td width=150>Added Date</td>
<td width=150>Owner</td>
</tr>";

Is it possible to not show that under such circumstances?

oesxyl
08-03-2008, 04:14 AM
yes, I guess that adding this two rows:


if($res && mysql_num_rows($res) > 0){ // < test if valid and not empty
$display .= "<table cellpadding=0 cellspacing=0>
<tr>
<td width=50>ID</td>
<td width=100>Category</td>
<td width=150>Name</td>
<td width=150>Added Date</td>
<td width=150>Owner</td>
</tr>";
while($row = mysql_fetch_array($res)){
$display .= "<tr>
<td>" . $row['iid'] . "</td>
<td>" . $row['catname'] . "</td>
<td>" . $row['name'] . "</td>
<td>" . $row['added_date'] . "</td>
<td>" . $row['username'] . "</td>
</tr>";
}
$display .= "</table>
<br>";
} // <- don't miss that


regards[/QUOTE]

fr600
08-03-2008, 04:44 AM
Awesome.. you are too good!! Thank you so very much.

dumpfi
08-03-2008, 02:28 PM
In case you wonder where your error was, it was here:
foreach ($cats as $rowc)
{
$res = <snip>
while($row = mysql_fetch_array($res)) // <-- here is your error
$display .= itemstable($res);
}Within the while you're fetching the first row. But instead of passing this row to itemstable, you pass the result set resource. itemstable then fetches the remaining rows to build a string. You never actually do anything with the first row, except for fetching it (and thus removing it) before the call to itemstable.

Easiest solution to this problem would have been to just remove the line while($row = mysql_fetch_array($res)).

dumpfi

fr600
08-03-2008, 03:09 PM
Wow, thanks a lot. Never thought there is such an easier solution to this...

oesxyl
08-03-2008, 04:21 PM
In case you wonder where your error was, it was here:
foreach ($cats as $rowc)
{
$res = <snip>
while($row = mysql_fetch_array($res)) // <-- here is your error
$display .= itemstable($res);
}Within the while you're fetching the first row. But instead of passing this row to itemstable, you pass the result set resource. itemstable then fetches the remaining rows to build a string. You never actually do anything with the first row, except for fetching it (and thus removing it) before the call to itemstable.

Easiest solution to this problem would have been to just remove the line while($row = mysql_fetch_array($res)).

dumpfi
look closer to the content of the function itemstable, :)

regards

dumpfi
08-03-2008, 07:13 PM
Sorry, but I can't see anything wrong in my writing. Care to elaborate?

dumpfi

oesxyl
08-03-2008, 10:43 PM
function itemstable($res){
echo "<table cellpadding=0 cellspacing=0>";
echo "<tr>";
echo "<td width=50>ID</td>";
echo "<td width=100>Category</td>";
echo "<td width=150>Name</td>";
echo "<td width=150>Added Date</td>";
echo "<td width=150>Owner</td>";
echo "</tr>";
while($row = mysql_fetch_assoc($res)){
echo "<tr>";
echo "<td>" . $row['iid'] . "</td>";
echo "<td>" . $row['catname'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['added_date'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "</tr>";
}
echo "</table>";
echo "<br>";
}

$display = "";
foreach ($cats as $rowc){
......
while($row = mysql_fetch_array($res))
$display .= itemstable($res);
}
echo "$display";
?>

see samething?

Sorry, but I can't see anything wrong in my writing. Care to elaborate?

dumpfi

regards

dumpfi
08-04-2008, 10:23 AM
OK, removing the usage of $display would enhance readability and make the intent of the code clearer, but the behavior of the script would still be the same.

I focused more on fixing the actual problem instead of making the code "good". Considering the latter, it might even be better to drop the echoing within itemstable altogether and instead return a string.

dumpfi



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum