Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Coder
    Join Date
    Apr 2006
    Posts
    17
    Thanks
    4
    Thanked 0 Times in 0 Posts

    First Row Missing!

    OK, I've a weird situation here. I've created a complete package of my problem.

    This is the database:
    Code:
    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:
    Code:
    <?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.

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    in itemstable function you use $row['iid'], $row['catname'], $row['name'], $row['added_date'], $row['username']
    PHP Code:
    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:
    PHP Code:
        $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
    Last edited by oesxyl; 08-03-2008 at 12:00 AM.

  • #3
    New Coder
    Join Date
    Apr 2006
    Posts
    17
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Well sir, I'm not an expert like you guys, could you please tell me how I can fix this?

    Thanks

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    try this way:
    PHP Code:
    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.

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

    regards
    Last edited by oesxyl; 08-03-2008 at 12:26 AM.

  • #5
    New Coder
    Join Date
    Apr 2006
    Posts
    17
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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
    Last edited by fr600; 08-03-2008 at 12:47 AM.

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    add a print_r in while and check the names returned as keys in $row and the values what would be displayed.
    PHP Code:
    $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

  • #7
    New Coder
    Join Date
    Apr 2006
    Posts
    17
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Yes I got a bunch of keys like:

    Code:
    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 )
    Code:
    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 )
    Code:
    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?

  • #8
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by fr600 View Post
    Yes I got a bunch of keys like:

    Code:
    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 )
    Code:
    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 )
    Code:
    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 Code:
    <?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

  • #9
    New Coder
    Join Date
    Apr 2006
    Posts
    17
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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.
    Last edited by fr600; 08-03-2008 at 01:47 AM.

  • #10
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by fr600 View Post
    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

  • Users who have thanked oesxyl for this post:

    fr600 (08-03-2008)

  • #11
    New Coder
    Join Date
    Apr 2006
    Posts
    17
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Ahh I see, thanks a lot, really!!

    What's the reason btw?

    Nevermind, just kidding..

  • #12
    New Coder
    Join Date
    Apr 2006
    Posts
    17
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Sorry, one last thing. Let's say, when one category is blank (with no record within the interval date), the following still shows up.

    Code:
            $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?

  • #13
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    yes, I guess that adding this two rows:
    PHP Code:
        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]

  • Users who have thanked oesxyl for this post:

    fr600 (08-03-2008)

  • #14
    New Coder
    Join Date
    Apr 2006
    Posts
    17
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Awesome.. you are too good!! Thank you so very much.

  • #15
    Regular Coder
    Join Date
    Jun 2004
    Posts
    565
    Thanks
    0
    Thanked 18 Times in 18 Posts
    In case you wonder where your error was, it was here:
    PHP Code:
    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
    Last edited by dumpfi; 08-03-2008 at 01:32 PM.
    "Failure is not an option. It comes bundled with the software."
    ....../)/)..(\__/).(\(\................../)_/)......
    .....(-.-).(='.'=).(-.-)................(o.O)...../<)
    ....(.).(.)("}_("}(.)(.)...............(.)_(.))Ż/.
    ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
    Little did the bunnies suspect that one of them was a psychotic mass murderer with a 6 ft. axe.

  • Users who have thanked dumpfi for this post:

    fr600 (08-03-2008)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •