MYSQL & PHP trouble with echoing tables

Question

So I am trying to echo out how many rows there are in a table with a COUNT command, but I purposely have no rows in the table right now to test the if statement, and it is not working, but worst, it makes the rest of the site not work(the page pops up but no text or numbers show up on it), when I added a row to the table, it worked fine, no rows = no work. Here is the piece of the code that doesn't work. Any and all help is highly appreciated.

    $query1 = mysql_query("
    SELECT *, COUNT(1) AS `numberofrows` FROM 
    `table1` WHERE `user`='$username' GROUP BY `firstname`,`lastname`
    ");
    $numberofrowsbase = 0;
    while($row = mysql_fetch_assoc($query1))
    {
        if(isset($row['numberofrows']))
        {
            $enteries1 = $enteries1;
        }else{
            $enteries1 =  $numberofrowsbase;
        }
        echo enteries1;
    }

If you're just learning PHP, please, do not learn the obsolete `mysql_query` interface. It's awful and is being removed in future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) can help explain best practices. Always be absolutely **sure** your user parameters are [properly escaped](http://bobby-tables.com/php) or you will have severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Oct 17 '14 at 21:04
So why doesn't it work? From your description, it seems to be working fine.. — Ali, Oct 17 '14 at 21:05
If there's no rows you will not get any results here. If something is depending on `$entries1` being set, that's going to be an issue. — tadman, Oct 17 '14 at 21:06
I can't see one line of this code that *works* in any kind of sensible way. You need to take a course (or just buy and follow a book) in programming, and object orientated programming would be a good choice. — worldofjr, Oct 17 '14 at 21:12
Bad SELECT as such: you shouldn't normally select * and group by at the same time. Normally you should define same fields in `select` which are in `group by`. — DarkSide, Oct 17 '14 at 22:28
If you see a blank page after an error, you must activate error reporting. — lxg, Oct 18 '14 at 10:23

score 0 · Answer 1 · edited May 23 '17 at 12:34

Maybe against my better judgement, I'm going to try and give you an answer. There's so many problems with this code ...

Do Not Use mysql_

The mysql_ extension is depreciated. You should use either mysqli_ or PDO instead. I'm going to use mysqli_ here.

SQL Injection

Your code is wide open to SQL injection where others can really mess up your database. Read How can I prevent SQL injection in PHP? for more information.

The Code

You don't need to count the rows with a SQL function, especially if you want to do something else with the data you're getting with the query (which I assume you are since you're getting a count on top of all the columns.

In PHP, you can get how many rows are in a result set using a built in function.

So all those things together. You should use something like this;

// Connect to the database
$mysqli = new mysqli($host,$user,$pass,$database); // fill in your connection details
if ($mysqli->connect_errno) echo "Error - Failed to connect to database: " . $mysqli->connect_error;

if($query = $mysqli->prepare("SELECT * FROM `table1` WHERE `user`=?")) {
    $query->bind_param('s',$username);
    $query->execute();
    $result = $query->get_result();

    echo $result->num_rows;
}
else {
    echo "Could not prepare query: ". $mysqli->error;
}

The number of rows in the result is now saved to the variable $result->num_rows, so you can use just echo this if you want, like I have in the code above. You can then go onto using any rows you got from the database. For example;

while($row = $result->fetch_assoc()) {
    $firstname = $row['firstname'];
    $lastname = $row['lastname'];
    echo "$firstname $lastname";
}

Hope this helps.

score 0 · Answer 2 · answered Oct 17 '14 at 22:13

Seems you have over complicated everything. Some good advise from worldofjr you should take onboard but simplest way to get total rows from a table is:

   SELECT COUNT(*) as numberofrows FROM table1;

There are several other unnecessary lines here and the logic is all bonkers. There is really no need to do

   $enteries1 = $enteries1;

This achieved nothing.

Do this instead:

    while($row = mysql_fetch_assoc($query1))
    {        
        if(isset($row['numberofrows']))
        {
        echo $row['numberofrows'];
        }
    }

MYSQL & PHP trouble with echoing tables

2 Answers2