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](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments) 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, May 25 '18 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, May 25 '18 at 10:47
$sql= "SELECT * FROM $username ORDER BY data DESC"; use like this — Arsalan Akhtar, May 25 '18 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, May 25 '18 at 11:36
hahahha , ok I am trying to make a type of web application so that the workers in my company can register their working days and see the results , and the only way thought was to make a table for each user — Avni Mula, May 25 '18 at 12:26
@Ende I have another file server.php where is the connection — Avni Mula, May 25 '18 at 12:28
Than it probably comes because you want to use a variable in your sql query — Ende, May 25 '18 at 12:36
Yeah I think that is the problem too , idk any other ways for the users to see their own data only , i could make different directoreis but if user1 after logging in , can easily type the url of user2 and do pretty much whatever he can , So I really need an idea , if you understand me — Avni Mula, May 25 '18 at 12:43
Just make different sql querys for each type of user, or you should have a lot of different users — Ende, May 25 '18 at 13:57

Luca · Answer 1 · 2018-05-25T10:28:06.703

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 '18 at 10:28

answered May 25 '18 at 10:21

Luca

296
2
19

1

Note that this answer (and the question) contain sql that easily be hijacked! Please prepare your statements before executing them! https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Loek May 25 '18 at 10:23
1

@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 – Luca May 25 '18 at 10:25
1

True! Just pointing out just in case somebody stumbles on this answer and doesn't know about it :) – Loek May 25 '18 at 10:26
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. – iainn May 25 '18 at 10:37
1

this is just a simple test it is not thet code I will be using but anyway thank you @Loek – Avni Mula May 25 '18 at 10:46

score 1 · Answer 2 · answered May 25 '18 at 10:21

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 '18 at 10:21

Ende

303
2
4
24

Note that this answer (and the question) contain sql that easily be hijacked! Please prepare your statements before executing them! https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Loek May 25 '18 at 10:24
2

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! – Loek May 25 '18 at 10:26

score 0 · Answer 3 · answered May 25 '18 at 10:21

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 '18 at 10:21

Alberto

1,348
2
14
28

Note that this answer (and the question) contain sql that easily be hijacked! Please prepare your statements before executing them! https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Loek May 25 '18 at 10:24

Martin · Answer 4 · 2018-05-25T11:21:53.683

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 '18 at 11:21

answered May 25 '18 at 10:58

Martin

2,326
1
12
22

Use double quotes or single quotes, but using single in double quotes wont work – Ende May 25 '18 at 11:16
1

Good catch, slight oversight on my part. – Martin May 25 '18 at 11:22

Using PHP variables as name of table in MySQL

4 Answers4