i am doing a mini project of social networking , i am having a doubt about table . let see , i need to provide post in their page. to do this,should i have to create table for each user or just create one table and use it for multiple users (data can be fetched by selecting particular user name and display it in their page ). which is the best way? my php code:
<?php
$query="select * from table_name where user=$username order by time desc;";
?>
It's best to just use 1 table of users and have a separate able for your posts. Your users table should have all the information for each specific users with 1 unique value that is automatically generated by the MySQL database. (Use auto-increment) And in the posts table you should have all the data for each post with a user_id column that holds the unique value from the users table, this way you know who posted it.
Here is a mockup table structure:
Users table:
uid | name | email
Posts table:
uid | user_id | message
user_id in the posts table should always be equal to some uid in the users table.
Every single table should always have some unique value that is assigned its primary value
I am very concerned with the security of your application. Prepared statements are WAY easier to use, and WAY more secure.
In the code snippet that you shared:
<?php
$query="select * from table_name where user=$username order by time desc;";
?>
this query is very insecure, as Bobby Tables would tell you. I'm not sure why type of database connection you are using, but I suggest PDO. I wrote a function that makes this very very easy, here is the snippet for that:
This is a file I usually call connection.php that you can import on any page you need to use your database.
<?php
$host = 'localhost';
$db = '';
$user = '';
$pass = '';
$charset = 'utf8';
$dsn = "mysql:host={$host};dbname={$db};charset={$charset}";
$opt = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);
function pdoQuery($query, $values = []) {
global $pdo;
if(!empty($values)) {
$stmt = $con->prepare($query);
$stmt->execute($values);
} else {
$stmt = $con->query($query);
}
return $stmt;
}
?>
This function allows you to EASILY use prepared statements by just including the connection.php page and writing queries in a way that is readable, clean, and secure. As I'm sure a lot of people reading this are not used to Prepared Statements or know how they work, the rest of this post will explain that.
One of the biggest differences here is that instead of using String Interpolation in your query, you will set variables as question marks
?, so your query looks like this:UPDATE table SET user=?instead ofUPDATE table SET user='$user'- and the variables will be sent later for safety, so this prevents SQL Injection.
This it the way your query would look now:
pdoQuery("SELECT * FROM `table_name` WHERE user=? ORDER BY `time` DESC", [$username]);
This is basically how it works:
pdoQuery(string $query, array $variables)
If you pass no variables, it automatically uses the query() function, if you do pass variables it automatically binds and executes the statements. No matter what query you do, the function always returns the query as an object, so you can act on it using any method you can normally use on a PDO query after the execute.
If you know how these work, you can stop reading here :) I put some exmaples below of some of the ways you can manipulate the return data to do what you need to do.
This function returns the object of the query you requested, so if you wanted to loop through all of the results of your query you use it like this:
$stmt = pdoQuery("SELECT * FROM `table_name` WHERE `user`=? ORDER BY time DESC", [$username])->fetchAll();
foreach($stmt as $row) {
$row['name']."<br>";
}
Or if you just wanted to get a single column from a specific row, you could use it like this:
$username = pdoQuery("SELECT `username` FROM `users_table` WHERE uid=? ORDER BY `time` DESC", [$uid])->fetchColumn();
Which will return the username from user where uid=$uid as a string
or if you wanted several values from 1 specific row, you could do
$user = pdoQuery("SELECT `username`,`name`,`email` FROM `users_table` WHERE uid=? ORDER BY time DESC", [$uid])->fetch();
Which will return to $user as an array that has the username, name, and email of the user.
You can also use this function to INSERT, UPDATE, or basically any type of query you can think of. This is how you insert:
pdoQuery("INSERT INTO `table_name` (`name`,`col2`, `col3`) VALUES (?,?,?)", [$name, $col1, $col2]);
Since writing this post, I have created a new database wrapper class called GrumpyPDO (Hosted on Github).
This class method returns the object of the query you requested, so if you wanted to loop through all of the results of your query you use it like this:
GrumpyPDO Long Syntax
$stmt = $db->run("SELECT * FROM `table_name` WHERE `user`=? ORDER BY time DESC", [$username])->fetchAll();
GrumpyPDO Short Syntax
$stmt = $db->all("SELECT * FROM `table_name` WHERE `user`=? ORDER BY time DESC", [$username]);
Loop:
foreach($stmt as $row) {
$row['name']."<br>";
}
Or if you just wanted to get a single column from a specific row, you could use it like this:
//Long Syntax
$username = $db->run("SELECT `username` FROM `users_table` WHERE uid=? ORDER BY `time` DESC", [$uid])->fetchColumn();
//Short
$username = $db->cell("SELECT `username` FROM `users_table` WHERE uid=? ORDER BY `time` DESC", [$uid]);
Which will return the username from user where uid=$uid as a string
or if you wanted several values from 1 specific row, you could do
//Long Syntax
$user = $db->run("SELECT `username`,`name`,`email` FROM `users_table` WHERE uid=? ORDER BY time DESC", [$uid])->fetch();
//Short Syntax
$user = $db->row("SELECT `username`,`name`,`email` FROM `users_table` WHERE uid=? ORDER BY time DESC", [$uid]);
Which will return to $user as an array that has the username, name, and email of the user.
You can also use this function to INSERT, UPDATE, or basically any type of query you can think of. This is how you insert (All DML's are similar):
$db->run("INSERT INTO `table_name` (`name`,`col2`, `col3`) VALUES (?,?,?)", [$name, $col1, $col2]);
