Display an int value from SQL database using PHP

Question

I'm busy with a school project where I need to register users. I created the database and added the tables and can add users. What I just can't get right is to display the next available user id in the table.

I'm using php to retrieve the highest value but when I use echo the variable won't show. There is no error, there is no output at all, just the rest of the page.

Here is the code:

<?php
        $db = mysqli_connect('localhost', 'root', '', 'design');
        $query = "SELECT MAX(userid) AS userid FROM users" or 
                 die(mysql_error());
        $highest_id = mysqli_query($db, $query);             
        echo $highest_id;
?>

The code successfully connects to the database, the column is called userid, it contains int values and there are other columns as well. All other code in the script runs perfectly, it's just this part that I can't get to work.

I have spent the last two days reading and searching for answers and I am at my wits end. Any help would be appreciated.

Thank you.

First learn how to fetch mysql data. For reference https://stackoverflow.com/questions/24028697/how-to-fetch-data-in-php-with-mysqli — denny, Nov 04 '17 at 20:45

ScaisEdge · Accepted Answer · 2017-11-05T06:12:55.577

0

could be your table is User and not Userid

$query = "SELECT MAX(userid) AS userid FROM users"

Anyway for fetching you should use eg:

$result  = mysqli_query($db, $query); 
$row=mysqli_fetch_array($result,MYSQLI_NUM);
echo $row[0];

edited Nov 05 '17 at 06:12

answered Nov 04 '17 at 20:43

ScaisEdge

131,976
10
91
107

Thank you. You were correct the table name was incorrect. It should have been users, not userid. Your fetching advice got the job done. Thank you for the help I really appreciate it. – Stephan Nov 04 '17 at 21:42

score 0 · Answer 2 · answered Nov 04 '17 at 20:51

The mysqli_query returns a general object that contains the results array. You have to use the mysqli_fetch_row.

<?php
  $db = mysqli_connect('localhost', 'root', '', 'design');
  $query = "SELECT MAX(userid) AS userid FROM userid" or die(mysql_error());
  $highest_id_query = mysqli_query($db, $query);

  var_dump($highest_id_query); // so you could check the object attributes

  //loop results from query
  while($row=mysqli_fetch_row($highest_id_query)){ 
   $highest_id = $row['userid'];
   echo $highest_id;
  }
?>

You could also use the sql statement: SELECT COUNT(*) FROM userid

Be sure to name your tables correctly! SELECT COUNT(*) FROM users

Display an int value from SQL database using PHP

2 Answers2