Using PHP variables as name of table in MySQL

Question

I want to use a PHP variable ( $username ) as the name of the SQL table I am creating. I need to use this because in my webpage each user needs to have his own table where he can put data , When I try to select the data from the table doesn't work, I have tried a lot of times but it is not working, can you help me with this problem?

$result = mysqli_query($mysqli, "SELECT * FROM `$username` ORDER BY id DESC");

and

$sql= "SELECT * FROM `$username` ORDER BY data DESC";

Neither of these do not work , Can you please help me?

This is the code I have

  <?php 
  session_start(); 

 if (!isset($_SESSION['username'])) {
  $_SESSION['msg'] = "You must log in first";
    header('location: login.php');
 }
 if (isset($_GET['logout'])) {
session_destroy();
unset($_SESSION['username']);
header("location: login.php");

}

$sql= 'SELECT * FROM '.$username.' ORDER BY data DESC';
  ?>
<!DOCTYPE html>
 <html>
    <head></head>
    <body>
<br/><br/>
 <div>
    <table align="center" width='100%' border=0>
        <tr bgcolor='#CCCCCC'>
            <td>Data</td>
            <td>Cantiere</td>
            <td>Pranzo</td>
            <td>Cena</td>
            <td>Hotel</td>
            <td>Macchina</td>
            <td>Note</td>
            <td>Edit/Delete</td>
        </tr>
        <?php 

         while($res = mysqli_fetch_array($result)) {         
            echo "<tr>";
            echo "<td>".$res['data']."</td>";
            echo "<td>".$res['cantiere']."</td>";
            echo "<td>".$res['pranzo']."</td>";
            echo "<td>".$res['cena']."</td>";
            echo "<td>".$res['hotel']."</td>";
            echo "<td>".$res['macchina']."</td>";    
            echo "<td>".$res['note']."</td>";
            echo "<td><a href=\"edit.php?id=$res[id]\">Edit</a> | <a 
 href=\"delete.php?id=$res[id]\" onClick=\"return confirm('Are you sure you 
want to delete?')\">Delete</a></td>";        
        }
        ?>
     </table>
 </div>

         </body>
 </html>

and i get the error :

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in index.php on line 36

When you say "it's not working", what exactly is happening? Turn on mysqli error reporting and you should get a better idea of what's actually going wrong. The three answers below all suggest changing to string concatenation, which won't make any difference. — iainn
– iainn, Commented May 25, 2018 at 10:35
True they don't make any difference , I have edited my question and put the code , I have written also the error i get — Avni Mula
– Avni Mula, Commented May 25, 2018 at 10:47
$sql= "SELECT * FROM $username ORDER BY data DESC"; use like this — Arsalan Akhtar
– Arsalan Akhtar, Commented May 25, 2018 at 11:01
Ok, the more I am reading into this, the more I start to wonder: Why do you want to do this anyways? — Luca
– Luca, Commented May 25, 2018 at 11:36

Ende · Accepted Answer · 2018-05-25 10:21:15Z

1

Try it like this

$sql= 'SELECT * FROM '.$username.' ORDER BY data DESC';

or

$result = mysqli_query($mysqli, "SELECT * FROM " .$username. " ORDER BY id DESC");

When you use a variable you need to use quotes

answered May 25, 2018 at 10:21

Ende

3113 gold badges4 silver badges25 bronze badges

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

2 Comments

Loek Over a year ago

Note that this answer (and the question) contain sql that easily be hijacked! Please prepare your statements before executing them! stackoverflow.com/questions/60174/…

Loek Over a year ago

And the Volkswagen CEO said the developers should write software that makes diesel engines a lot cleaner when they are run in test mode. That still doesn't make it right to write bad software!

Luca · Accepted Answer · 2018-05-25 10:28:06Z

1

You should try something like this:

$result = mysqli_query($mysqli, "SELECT * FROM " . $username . " ORDER BY id DESC");

Because SQL does not know what $username is

This goes for both of the query's

As suggested by Loek: Note that this answer (and the question) contain sql that easily be hijacked! Please prepare your statements before executing them!

edited May 25, 2018 at 10:28

answered May 25, 2018 at 10:21

Luca

2863 silver badges19 bronze badges

5 Comments

Loek Over a year ago

Note that this answer (and the question) contain sql that easily be hijacked! Please prepare your statements before executing them! stackoverflow.com/questions/60174/…

Luca Over a year ago

@Loek that is very true, but that is not the point of this question. For as far as we know OP has just picked thest 2 query's to give us an example, and keep the question as short as possible

Loek Over a year ago

True! Just pointing out just in case somebody stumbles on this answer and doesn't know about it :)

iainn Over a year ago

PHP already expands variables in double-quoted strings - how will this help? Also note that prepared statements can't take a table name as a parameter, so those won't help with security.

Avni Mula Over a year ago

this is just a simple test it is not thet code I will be using but anyway thank you @Loek

Alberto · Accepted Answer · 2018-05-25 10:21:09Z

0

Try to add string concatenation:

$result = mysqli_query($mysqli, "SELECT * FROM " . $username . " ORDER BY id DESC");

and

$sql= "SELECT * FROM " . $username . " ORDER BY data DESC";

answered May 25, 2018 at 10:21

Alberto

1,4082 gold badges18 silver badges29 bronze badges

1 Comment

Loek Over a year ago

Note that this answer (and the question) contain sql that easily be hijacked! Please prepare your statements before executing them! stackoverflow.com/questions/60174/…

Martin · Accepted Answer · 2018-05-25 11:21:53Z

0

You need to concatinate your variable into your select statement, i.e.

$sql="SELECT * FROM ".$username." ORDER BY id DESC";

And

$sql="SELECT * FROM ".$username." ORDER BY data DESC";

edited May 25, 2018 at 11:21

answered May 25, 2018 at 10:58

Martin

2,4221 gold badge14 silver badges23 bronze badges

2 Comments

Ende Over a year ago

Use double quotes or single quotes, but using single in double quotes wont work

Martin Over a year ago

Good catch, slight oversight on my part.

Collectives™ on Stack Overflow

Using PHP variables as name of table in MySQL

4 Answers 4

2 Comments

5 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

5 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related