mySQL statement not running in PHP variable declaration

Question

In the following code I'm attempting to connect to my database, pull the maximum ID from my table and then generate a random number using the the rand() function. The code successfully connects me to the the database but when I try to call for the maximum ID it won't return a value.

When I try to echo the variable, it returns SELECT MAX(id) FROM 'file'.

<?php

// Connect to the database
        $dbLink = new mysqli('localhost', 'username', 'password', 'database');
        if(mysqli_connect_errno()) {
            die("MySQL connection failed: ". mysqli_connect_error()); }


    $amount = "SELECT MAX(id) FROM 'table'";
    $rannmr = rand(1, $amount);


// Close the mysql connection
    mysqli_close($dbLink);

?>

Any help in resolving this would be appreciated.

Funk Forty Niner · Accepted Answer · 2014-10-04 17:17:57Z

2

When I try to echo the variable, it returns SELECT MAX(id) FROM 'file'.

Firstly, you are using the wrong identifier for FROM 'table' being single quotes.

If table is indeed the table's name, wrap it in backticks, your question shows file.

$amount = "SELECT MAX(id) FROM `table`";

Either way, you cannot use quotes around a table name. It appears you are using file as your table name.

So if table is only an example and it is called file let's just say, you would do:

$amount = "SELECT MAX(id) FROM `file`";

or

$amount = "SELECT MAX(id) FROM file";

Then, you also need to query, using mysqli_query() which you are not doing.

$amount = mysqli_query($dbLink,"SELECT MAX(id) FROM `file`");

Or Object oriented style:

$amount = $dbLink->query("SELECT MAX(id) FROM `file`");

if($amount){
    echo "Success!";
}else{
    die('Error : ('. $dbLink->errno .') '. $dbLink->error);
}

See example #1 from http://php.net/manual/en/mysqli.query.php

Use or die(mysqli_error($dbLink)) to mysqli_query() which would have signaled the error.

http://php.net/manual/en/mysqli.error.php

Edit:

Try the following. You may need to modify $row[0] and rand(0,$count) as 1 depending on the column number.

$result = $dbLink->query("SELECT MAX(id) FROM mytable")
while ($row=$result->fetch_row()) { $count = $row[0]; }
$random = rand(0,$count);
echo $random;

edited Oct 4, 2014 at 17:17

answered Oct 4, 2014 at 16:35

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sangeet Over a year ago

error reporting returns the following error "rand() expects parameter 2 to be long, object given..."

Funk Forty Niner Over a year ago

@Sangeet Why not just use MySQL's RAND() function? I.e. ORDER BY RAND()

Sangeet Over a year ago

I would like speed to be of priority.

Funk Forty Niner Over a year ago

@Sangeet Reload my answer and look near the bottom under Edit

Sangeet Over a year ago

The script echo's "0". Modifying $row[0] and rand(0, $count) to 1 echo's "Notice: Undefined offset: 1.." and "0".

|

Suchit kumar · Accepted Answer · 2014-10-04 16:36:24Z

0

use this:

$amount = "SELECT MAX(id) FROM table";

answered Oct 4, 2014 at 16:36

Suchit kumar

11.9k3 gold badges26 silver badges46 bronze badges

Comments

Andreas Krischer · Accepted Answer · 2014-10-04 16:38:01Z

0

You forgot to execute the MySQL-query:

$amount = $dbLink->query("SELECT MAX(id) FROM table")->fetch_assoc();
$rannmr = rand(1, $amount[0]);

answered Oct 4, 2014 at 16:38

Andreas Krischer

1448 bronze badges

Comments

meda · Accepted Answer · 2014-10-04 16:39:18Z

0

You never executed the query, you need more logic

if ($result = mysqli_query($dbLink, "SELECT MAX(id) as amount FROM `table`")) {
    printf("Select returned %d rows.\n", mysqli_num_rows($result));
    if ($row = mysqli_fetch_assoc($result)) {
        $amount = $row['amount']; 
        $rannmr = rand(1, $amount);            
    }else{
        echo 'no row found';
    }
}
mysqli_close($dbLink);

answered Oct 4, 2014 at 16:39

meda

45.5k14 gold badges96 silver badges123 bronze badges

Comments

GitKidd · Accepted Answer · 2014-10-04 16:40:15Z

I didn't seem to see the line of code which actually does the query:

Try this: Using the object-oriented mysqli approach

<?php

// Connect to the database
    $dbLink = new mysqli('localhost', 'username', 'password', 'database');
    if(mysqli_connect_errno()) {
        die("MySQL connection failed: ". mysqli_connect_error()); }


$amount = "SELECT MAX(id) as max_id FROM 'table'";

// Do the actual query : 
$run_query = $dbLink->mysql->query($amount);
// Retrieve the values:
$result = $run_query->fetch_array(); 
// Do the rand function together with the retrieved value
$rannmr = rand(1, $result['max_id']);

// Now you can echo the variable:
echo $rannmr;



// Close the mysql connection
mysqli_close($dbLink);

?>

Thanks!!

Collectives™ on Stack Overflow

mySQL statement not running in PHP variable declaration

5 Answers 5

7 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related