SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question PHP MSQL While Loop only returns first row

    I don't know what I did wrong. Your help is appreciated.


    Code PHP:
    require('_db.php'); // get the db connection info
     
    $metricsql = "SELECT * FROM $metrics_tableName ORDER BY '$metrics_order_col'";
    $metrics = array();
    $result=mysql_query($metricsql)or die(" Error: Could not connect to metrics table".$metricsql.mysql_error());
     
     
         //Begin metrics loop 
    	 while( $row = mysql_fetch_array($result)){
     
     		print_r($row);
    	//return $metrics[$i]['metric_label'].'here';
    	print $row['metric_label'].'here';
    	return $row['metric_label'];
    	return count($row);
    	}//end metrics loop
     
    }

    Here is my DB Schema
    Code MySQL:
    id_metric 	metric_label 	date_ini 	date_fin 	order
    	Edit 	Delete 	1 	originality 	0000-00-00 00:00:00 	2009-05-17 15:50:07 	0
    	Edit 	Delete 	2 	creativity 	0000-00-00 00:00:00 	2009-05-17 15:56:47 	0
    	Edit 	Delete 	3 	neatness 	0000-00-00 00:00:00 	2009-05-17 15:56:47 	0
    	Edit 	Delete 	4 	clarity of written description 	0000-00-00 00:00:00 	2009-05-17 15:56:47 	0
    	Edit 	Delete 	5 	skill level involved 	0000-00-00 00:00:00 	2009-05-17 15:56:47 	0
    	Edit 	Delete 	6 	adherence with the rules 	0000-00-00 00:00:00 	2009-05-17 15:56:47 	0

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    can't help you with the php, but you should be aware that this won't work the way you think it will --
    Code:
    ORDER BY '$metrics_order_col'
    no matter what value $metrics_order_col might have, if you put quotes around it, it's a string, and then all the rows will be sorted by the same value, i.e. unpredictably
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    can't help you with the php, but you should be aware that this won't work the way you think it will --
    Code:
    ORDER BY '$metrics_order_col'
    no matter what value $metrics_order_col might have, if you put quotes around it, it's a string, and then all the rows will be sorted by the same value, i.e. unpredictably
    I removed the quote. Thanks.
    I'm still returning only one row however.

  4. #4
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why are you calling return (and twice)? It exits the script if not called from within a function or included file.

  5. #5
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @ Decowski. It's in a function. I was just testing the return to see if I would get different values. Commenting out one of the returns doesn't seem to make a difference. hmm..



    Code PHP:
    function show_metrics(){
     
    require('_db.php'); // get the db connection info
     
    // get votes, values, ips for the current rating bar
    $metricsql = "SELECT * FROM $metrics_tableName ORDER BY $metrics_order_col ASC";
    $metrics = array();
    $result=mysql_query($metricsql)or die(" Error: Could not connect to metrics table</BR>
    The offending query was<pre>".$metricsql."</pre>".$metricsql.mysql_error());
     
     
         //Begin metrics loop 
    	 while( $row = mysql_fetch_array($result)){
     
     		print_r($row);
    	//return $metrics[$i]['metric_label'].'here';
    	print $row['metric_label'].'here';
    	return $row['metric_label'];
    	//return count($row);
    	}//end metrics loop
     
    }

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_fetch_array() returns a single row. If you want your function to return all rows, you will need to collect them all into an array.

    PHP Code:
    $rows =  array();
    while(
    $row mysql_fetch_array($result)) {
        
    $rows[] = $row;
    }
    return 
    $rows

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    echo the sql query in $metricsql then access the db via phpmyadmin and run the sql against the db in phpmyadmin, does it still only give one row? In any case that should 100&#37; rule out any errors in your php
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    mysql_fetch_array() returns a single row. If you want your function to return all rows, you will need to collect them all into an array.

    PHP Code:
    $rows =  array();
    while(
    $row mysql_fetch_array($result)) {
        
    $rows[] = $row;
    }
    return 
    $rows
    Aha! This was it. Thanks!

  9. #9
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could someone please explain to me why crmalibu's suggestion worked but this example here wouldn't works without making $row and array via row#= array() and $row =[]?

    It would better my understanding so I could avoid these things in the figure. Thank you.
    Code PHP:
    Example : select.php
    Source code : select.phps, contact.txt
     
    <?php
    include 'config.php';
    include 'opendb.php';
     
    $query  = "SELECT name, subject, message FROM contact";
    $result = mysql_query($query);
     
    while($row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        echo "Name :{$row['name']} <br>" .
             "Subject : {$row['subject']} <br>" .
             "Message : {$row['message']} <br><br>";
    }
     
    include 'closedb.php';
    ?>
    from here: http://www.php-mysql-tutorial.com/wi...-database.aspx


Bookmarks

Posting Permissions

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