How to retrieve information from a database

Question

I have the following code to retrieve information from a database. It connect connects to the database, but when I try to display the information I don't see anything. I only get the dash lines. I think the problem is in the while loop, I just can not see where my mistake is. Can anyone help??

 $connection = mysql_connect("127.0.0.1","root","xxxxxxxx");
        if (!$connection)
        {
        printf("Can't connect to MySQL Server.", mysqli_connect_error());

        }


    mysql_select_db('xxxxxx') or die ('Could not select database');
        echo 'You have been connected succesfully to your database';

        $query = "SELECT *FROM testing";
        $result = mysql_query($query) or die("Query fail:".mysql_error());


        while ($row = mysql_fetch_array($result));
        {
            echo $row['age']."--".$row['sex']."--".$row['id']."--".$row['country']."--".$row['sport']." ";
        }

        ?>

Looking at the code it "should" work unless you're encountering an error or no results in your query. Is this an ajax loaded page you are calling from a parent page? if so you need to use the ajax result and set a div or element html. — Silvertiger, Jun 10 '14 at 15:48
did you try a `var_dump($row)` to see what you really got from the query? remember that the array keys are case-sensitive. If you get the `--` as output, then your loop **IS** working, you're probably just accessing invalid/undefined array keys. If there were NO results, then the loop wouldn't run at all and you wouldn't even get the `--`. — Marc B, Jun 10 '14 at 15:49
I see you have your wildcard and FROM with no space, is that a typo and should be "* FROM" with the spaee added? — Silvertiger, Jun 10 '14 at 15:50
I did not try that @MarcB. I am new with php, so there are many stuff that I/m still learning — Alexander, Jun 10 '14 at 15:50
In these cases I do var_dump($result) to make sure that MySQL has in fact retrieved something. Using *FROM or * FROM, i.e. with or without space doesn't matter, I just checked it on my MySQL console and it returns results either way. But just in case you can try to put a space, maybe your version of MySQL does need a space. — zeeshan, Jun 10 '14 at 15:54
The mysql functions you are using are depreciated and will "go away" soon. Best to switch to MySQLi or PDO. I use this one: http://codular.com/php-mysqli — Len_D, Jun 10 '14 at 15:54
I got the following when I do var_dump($result): resource(4) of type (mysql result) — Alexander, Jun 10 '14 at 15:58
@zeeshan I got the following when I do var_dump($result): resource(4) of type (mysql result) — Alexander, Jun 10 '14 at 16:00
Please try `var_dump($row)` instead, doing `var_dump($result)` is meaningless *(you should put this into the `while` loop to get the best results)*. — Petr R., Jun 10 '14 at 16:00
@PetrR. with var_dump($row) i got the following: bool(false) — Alexander, Jun 10 '14 at 16:03
There should be no semicolon (`;`) in this line: `while (...);` — Petr R., Jun 10 '14 at 16:08
@PetrR. thank you very much. It works now. I just have an extra question. How is the information stored in the array???. in this case, I have four rows. would every row correspond to an index in the array? — Alexander, Jun 10 '14 at 16:11

score 1 · Answer 1 · edited May 23 '17 at 11:49

You should remove the semicolon (;) after while(...), so your final code will look like:

while ($row = mysql_fetch_array($result))
{
    echo $row['age']."--".$row['sex']."--".$row['id']."--".$row['country']."--".$row['sport']." ";
}

Whatever is in your while loop (between { and }) will be called as many times as many rows will return your query. For example, if your query will return 4 rows, then your code (echo ...) will be repeated 4 times. In the first run, variables $row["age"], $row["sex"], $row["id"] and $row["country"] will contain the results for the first row. On the second run, these variables will contain the results of the second row...

Also, please, don't use the mysql_* functions, they are deprecated and will be removed in the future versions of PHP. Use MySQLi or PDO instead. See Why shouldn't I use mysql_* functions in PHP? for more details.

score 0 · Answer 2 · answered Jun 10 '14 at 15:55

0

I was having a similar issue, turns out my DB had no information in it, and one of my value names was incorrect. Can you confirm there are valid values in your DB?

answered Jun 10 '14 at 15:55

user3604560

11
3

score 0 · Accepted Answer · answered Jun 10 '14 at 16:18

Use mysqli

$db = new mysqli("localhost","admin","XXXXXX","test");
if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

$query = "SELECT * FROM `testing`";  

if(!$result = $db->query($query)){
    die('There was an error running the query [' . $db->error . ']');
}

// echo $result->num_rows;  Uncomment to make sure there is data being ruturned

while($row = $result->fetch_assoc()){
    echo $row['age']."--".$row['sex']."--".$row['id']."--".$row['country']."--".$row['sport']." ";   
}

How to retrieve information from a database

3 Answers3