2 SQL get from db

Question

a have small problem with my script.

I have data on database. But I need to use the data from one table in the same table. And I do not know how to make it exactly to make it work I tried I created some features but they wrote it to me.

$sql = "SELECT header,text,user_id FROM posts";
$result = $con->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {   
 <? echo $row["text"]; ?>

}
}

This code work But when I put it again in it to find out the name of the user so it does not

$sql = "SELECT header,text,user_id FROM posts";
$result = $con->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {   
 <? echo $row["text"]; ?>


 $sql = "SELECT name FROM users WHERE id='$row["user_id"]'";
  $result = $con->query($sql);
  if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) { 
     <? echo $row["name"]; ?>
   } 
  }    
}
}

This not working..I d know how fixed.

Tutorials how to do it better will be appreciated.

Thanks

You should read about prepared statements, which gets around most of the problems you are currently having. — Tim Biegeleisen, Feb 01 '19 at 02:32

score 2 · Answer 1 · answered Feb 01 '19 at 02:39

The easiest way would be to join the table within the first statement:

$sql = "SELECT header,text,user_id,name FROM posts p LEFT JOIN users u ON u.id = p.user_id";
$result = $con->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {   
    echo $row["text"];
  }
}

You should also learn something about Joins. And as @TimBiegeleisen stated out use prepared statements

score 0 · Answer 2 · answered Feb 01 '19 at 10:24

0

I try this and not work to..

$sql = "SELECT id,header,text,author,u.username as autorname FROM posts p LEFT JOIN users u ON u.username = p.author";
$result = $con->query($sql);



<? echo "" . $row["autorname"]. ""?>

any Data is not show

answered Feb 01 '19 at 10:24

TheGridCoder

41
1
3

Why are you trying to join the tables on the username when you stated in your question that you dont't know the name? Please edit your question and add the structure of your db tables `posts` and `users`. – KHansen Feb 01 '19 at 12:19

2 SQL get from db

2 Answers2