Adding table row value into a variable using PHP and MySQL

Question

This will need some examples:

For example this is my MySql dump.sql file:

CREATE TABLE IF NOT EXISTS `user` (
`id` int(10) unsigned NOT NULL,
`email` varchar(30) NOT NULL,
`username` varchar(30) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO `user` (`id`, `email`, `username`) VALUES
(1, '[email protected]', 'test1'),
(2, '[email protected]', 'test2'),
(3, '[email protected]', 'test3');

ALTER TABLE `user`
ADD PRIMARY KEY (`id`);

ALTER TABLE `user`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;

Then i have a db.php file that contains DB connection and a function:

<?php
 define('DB_HOST','localhost');
 define('DB_USER','test');
 define('DB_PASS,'test');

 $conn = mysql_connect(DB_HOST, DB_USER, DB_PASS);
 if(!$conn) {
    die('Could not connect: ' . mysql_error());
 }
 mysql_select_db(DB_USER, $conn);

function get_all($sql)
{
  @$db = new mysqli(DATABASE_HOSTNAME, DATABASE_USERNAME, DATABASE_PASSWORD);
  global $db;
  $q = mysqli_query($db, $sql);
  while (($result[] = mysqli_fetch_assoc($q)) || array_pop($result)) {
    ;
  }
  return $result;
}

After that i have a sql query that looks like this:

$users = get_all("SELECT * FROM test.user ORDER BY id");

This gives me all the items from the 'user table'.

After that i have an index.php file that uses require_once('db.php') and has the following code to call out all items from the database table like this:

<form>
  <table>
    <thead>
      <tr>
        <th>ID</th>
        <th>Email</th>
        <th>Username</th>
      </tr>
    </thead>
    <tbody>
      <?php if (!empty($users)) foreach ($users as $user) { ?>
       <tr>
         <td><?= $user["id"]; ?></td>
         <td><?= $user["email"]; ?></td>
         <td><?= $user["username"]; ?></td>
       </tr>
      <?php } ?>
    </tbody>
  </table>
</form>

All of this works to print out a table with a list of all the users. And now to my question: How can i add the "id", "email" or "username" into some kind of a global variable so i can use the "id" later.

For example i've tried to add the id into $_SESSION:

<td><?= $_SESSION["user_id"] = $user["id"]; ?></td>

And that only shows me the last id if i echo it out:

 <?php echo $_SESSION["user_id"]; ?>

...which gives me "3"

What im asking: is there a way to get that "ID", "email" or "username" from each table row and add that into a variable, so i can use it later in other sql queries to either UPDATE or DELETE row using the "ID"?

For example, if i wanted to delete the 2nd user from the "user" table i could use something like:

DELETE FROM test.user WHERE ID = $user_id;

How do i get that $user_id?

what's wrong with storing all of the variables into a session? (side note: you keep jumping back and forth between mysql and myqli. don't do that. mysql_ and much of your code is deprecated. — nomistic
– nomistic, Commented May 25, 2015 at 0:57

sark9012 · Accepted Answer · 2015-05-25 00:58:46Z

1

Within each foreach loop, you could store the session data as follows:

$_SESSION[$user['id']]['email'] = $user['email'];
$_SESSION[$user['id']]['username'] = $user['username'];

Then when you want to retrieve the email or username, you can just pass the ID in.

For example, if the ID was 7, to get the email address, you would write:

$_SESSION[7]['email']

And that would return the email address of the user with an ID of 7.

That should help.

The reason $_SESSION['user_id'] is only showing the last ID is because each time the foreach loop runs, it is overwriting this until you reach the final iteration of the loop, and then that is what you echo out.

answered May 25, 2015 at 0:58

sark9012

5,78921 gold badges68 silver badges103 bronze badges

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

4 Comments

User_T Over a year ago

You mean something like this: <td><?= $_SESSION[$user['id']]['email'] = $user['email']; ?></td>. How would you call that out in another .php webpage? echo $_SESSION['email'] or something else? My big question is how would you add the "id" number into the variable so i could use that to delete a single row from table.

sark9012 Over a year ago

You'd need to know what ID you want to find the email for. So as I said, you could get the email which has ID 7 by using $_SESSION[7]['email']...

User_T Over a year ago

Oh i see what you ment. Tried it and it worked. Ofc i needed to work a little with the session itself because i had a lot of items in it from other forms. session_destroy, session_unset and session_start and different pages helped me out on that.

sark9012 Over a year ago

Glad to have helped :) just remember when you are looping through data and want to write it elsewhere, you need some type of count to increment!

Collectives™ on Stack Overflow

Adding table row value into a variable using PHP and MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related