PHP MySQL query result into a variable and then echo ($variable)

Question

wish to check the last id (cat_id) in a table (category) and insert that result into a variable that I can echo.

My intention is to create a record last cat_id +1 as long as it doesnt already exist of course.

What I thought I should do was something like this;

<?php
   require "mydbdetails.php";
   $query="SELECT cat_id FROM category ORDER BY cat_id DESC LIMIT 1";
   $result=mysql_query($query);
   echo ($result);

 ?>

But oh no, nothing so simple. The echo was only to check I had the correct result (in phpmyadmin it returns the desired number)

Then I was hoping to be able to, with a simple html form, was to ask if the user wanted to add a category through a text box:

addrec.html:

<form action="addrec.php" method="post">
 Category: <input type="text" name="category">
 <input type="submit">
 </form>

addrec.php:

<?php
  require "mydbdetails.php";
  $new_id = $result + 1;
  $query="INSERT INTO category VALUES ($new_id, 'Fruits')";
?>

Be sure to read this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 It's not an answer to your question, but very important as you continue to learn php/MySQL. — TecBrat, Jun 10 '14 at 14:54
Also don't forget that mysql_ functions are deprecated. Use mysqli_. — Athanasios Emmanouilidis, Jun 10 '14 at 15:08

score 0 · Answer 1 · answered Jun 10 '14 at 14:59

0

echo $result[0]['cat_id'] i think

answered Jun 10 '14 at 14:59

user3712744

1
1

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

Please consider using PDO query Syntax instead of the old deprecated mysql_query.

If you make a PDO connection and store it in the $conn object ( pretty similar to what you already have in mydbdetails.php) just do:

   $query=$conn->query("SELECT cat_id FROM category ORDER BY cat_id DESC LIMIT 1");
   $result=$query->fetchAll(PDO::FETCH_COLUMN,0);
   echo ($result[0]);

score 0 · Answer 3 · answered Jun 10 '14 at 15:04

First of all, you should really be using mysqli instead of mysql because mysql is deprecated and will be removed in PHP 5.5.

Of course the PDO would be better, but i think that you're so new that it would be a bit to much right now.

Basically, you're firing a Query, but you don't tell the query to what connection. You're doing this:

$result=mysql_query($query);

What it should be is

$result=mysqli_query($link, $query);

Where $link is the variable where you're setting up the database connection in the mydbdetails.php file.

Without the connection the Query doesn't know where to get the data from.

But if OOP isn't new to you, the answer from @amenadiel is better because it's an OOP way.

Further, there is no need for your $new_id = $result + 1; line.

IDs should almost always set to Auto Increament in the database, so this line will be done automatically in the database when you're adding a new dataset.

More information here

score 0 · Answer 4 · answered Jun 10 '14 at 15:05

Hope this helps

<?php
  require "mydbdetails.php";
  $query="SELECT cat_id FROM category ORDER BY cat_id DESC LIMIT 1";
  $result=mysql_query($query);

  $i;
  while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
      var_dump($row);  /* dump rows*/

      /* or add to array to use later */
      $results[$i] = $row; // add to array
      $i++; // +1 the counter to increment the array
  }
?>

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

You must first know as a developer, that mysql extension in php will be fully deprecated in the future as it is already in the newer php versions. So use instead Mysqli extension and PDO for sanitation and securer code for your database.

As it goes to your question:- Try the following ;

   // Make a MySQL Connection
   $query = "SELECT cat_id FROM category ORDER BY cat_id DESC LIMIT 1";
   //assign result to a variable
   $result = mysql_query($query) or die(mysql_error());
   //fetch result as an associative array
   $row = mysql_fetch_array($result) or die(mysql_error());
   echo $row['cat_id'];

You can assign it to avariable $row['cat_id'] = $catId; like that and use it .

score 0 · Answer 6 · answered Jun 10 '14 at 15:20

Try to write your scripts in mysqli_ or pdo functions. Work around in mysql_ function (not recommended)

<?php
   require "mydbdetails.php";
   $query="SELECT cat_id FROM category ORDER BY cat_id DESC LIMIT 1";
   $result=mysql_query($query);
   if (mysql_num_rows($result) > 0) {
      $row = mysql_fetch_array($result);
      $cat_id = $row['cat_id'] + 1; 
   } else {
      $cat_id = 1; 
   }

 ?>

score -1 · Answer 7 · answered Jun 10 '14 at 14:53

-1

cat_is is the primary key? If yes you can set that column as auto increment. You can try this

https://php.net/manual/en/function.mysql-result.php

answered Jun 10 '14 at 14:53

Pramod Sivadas

873
2
9
28

That's not a answer to his Question, though you're right, but this belongs to the comments – KhorneHoly Jun 10 '14 at 14:56

PHP MySQL query result into a variable and then echo ($variable)

7 Answers7