Php Mysql select from 2 tables

[PNewCode]

Hello. A little guidance if you don't mind and with my great appreciation.

The objective is to have to links, that use 2 different functions, selecting from 2 different tables. Each link goes to a different page.
So far, this is able to grab the id for the first link but not the second. Any thoughts of what I need to do different?
NOTE: To be specific, it's the line to delete messages that isn't grabbing the id

 

<?php
// database info here

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT unique_id, fname, lname AND user_id FROM users, messages";
$result = $conn->query($sql);


$resultt = mysqli_query($conn, "select COUNT(unique_id) AS count FROM `users`");

if(!$result) {
    die('Error: ' . mysqli_error($link));
} else {
    $num_rows = mysqli_fetch_assoc($resultt);
    // echo it
    echo "<font color='yellow'>Total Users</font>: <font color='aqua'><b>" . $num_rows['count']."</font></b></br></br>";
}

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) { ;  ?> <font color='orange'>User Name: 
      </font><font color='black'><?php 
echo htmlspecialchars($row["fname"]);  ?>&nbsp;<?php 
echo htmlspecialchars($row["lname"]);  ?>


<br>


      <font color='orange'>ID: 
      </font><font color='black'> 



<?php echo nl2br(htmlspecialchars($row["unique_id"]). "
<a href='delete-user.php?id=".$row['unique_id']."'>Remove User</a>&nbsp;




<a href='delete-messages.php?id=".$row['user_id']."'>Clear User Chat</a>
___________<br><br>",60);
  }
} else {
  echo "0 Users";
}
$conn->close();
?>

Any help is very much appreciated

Edited November 21, 2022 by PNewCode

[ginerjm]

I believe this is going to be rejected:

$sql = "SELECT unique_id, fname, lname AND user_id FROM users, messages"; 

because the word AND is not a column name and there is no usage of it in selecting columns to be queried.  Not sure why you are using it.

I am assuming that users and messages are your 2 tables.

How are the 2 tables connected?  Is user_id the key to the messages table and you want to select each user and his/her associated messages?  I would do something  like this:

$q = "SELECT u.unique_id, u.fname, u.lname FROM users u, messages m
		where u.unique_id = m.user_id
		order by u.lname,u.fname";

 

[PNewCode]

@ginerjmhere is the break down.
The two tables are "users" and "messages"

The first link does grab the id (from table "users")
<a href='delete-user.php?id=".$row['unique_id']."'>Remove User</a>

But the second one does not (from table "messages")
<a href='delete-messages.php?id=".$row['user_id']."'>Clear User Chat</a>

So I'm guessing, like you said is in the 
$sql = "SELECT unique_id, fname, lname AND user_id FROM users, messages";

But I'm not sure how to change that to work for that second link
<a href='delete-messages.php?id=".$row['user_id']."'>Clear User Chat</a>

[Barand]

As @ginerjm stated, the AND is wrong - it should be a comma.

Also the use of "... FROM users, messages" will result in a cartesian join. If you have a 100 users and each have 1 message, the query will return 10,000 rows (each user joined to each message). You need to specify the join criteria.

What are the structures of your 2 tables? ie the output from these two queries....

• DESCRIBE users;
• DESCRIBE messages;

[ginerjm]

None of that made any more sense than your first post.  I don't know what you are referring to as "the first link".  YOu started with a query statement that needed correcting and you are posting something about anchor tags.

[PNewCode]

@ginerjm I stated what is "The first link". It says it specifically in my second post on this. See this from that post...

The first link does grab the id (from table "users")
<a href='delete-user.php?id=".$row['unique_id']."'>Remove User</a>

But the second one does not (from table "messages")
<a href='delete-messages.php?id=".$row['user_id']."'>Clear User Chat</a>

Now, the problem using the comas is that the first table users has multiple columns to choose from named unique_id, fname, lname
and the second table messages only has one I am using, named user_id

So if I just split them up in comas, then there is nothing to sort which results are from just the two tables.
If I put it as you did with the u and m, then the links (see above) wont pull those id's from the tables.

I'm not sure why you don't know why you don't know what I mean by first link when I said it directly above that line of code

So the objective, is for the first link, it grabs the id from users
and the second link, it grabs the id from messages

NOTE: the first link works just fine by the way

Edited November 21, 2022 by PNewCode

[ginerjm]

Since the 2 ids have to match in order to have a successful query I did not include the second id field in the query results of the updated query I gave you.

I fail to see how your first link gives you anything since the first query cannot possibly execute until you change it.

PS - your html is horribly outdated.  The font tag has been long gone.  Use css, either as defined ids or classes, or use it inline on your statements, with the former being the better way.

PS - are you getting any error messages or do you not have it turned on?

Edited November 21, 2022 by ginerjm

[PNewCode]

@ginerjm As to WHY it works, that I can't tell you. I had a page already made that works for one query and I just added the extra stuff to try for a different table in the same DB.

The ID's can't be the same in both tables because one is for a user id, the second is for a message id.

So the first one deletes the user from the DB, and the second one just clears all their messages. I cannot change the column names because it would break the whole build and I would rather make 2 delete pages than to rewrite 60+ other pages that are using the same databases.

So this is why I'm trying to get both to work in the same page

[ginerjm]

You could have told me that my first assumption was incorrect.  Now that I know this I have to ask: What is the connection between rows in the users table and the rows in the message table?  You have to be able to link them together in order to do a query that joins the data

[PNewCode]

@ginerjm after looking into this, it appears what I want to do is impossible without having completely different php sections. So I am going to just make 2 smaller php files and link to each one instead of making a mess of one page lol

Thanks for your time everyone

[ginerjm]

I don't think the problem is that you have trouble with your php files.  It is could be the design of your database.

[Barand]

45 minutes ago, ginerjm said:

It is could be the design of your database.

We'll probably never know. I did ask for his table structures but that got ignored. Good luck.

[PNewCode]

@Barand sorry I didn't see it. My apologies. 

Table Users has the id as unique_id

Table Messages has the id as user_id

Somehow one user has both of those. But I just needed to delete all from the table messages under user_id="user id here" and grab it from the unique_id from the other table.

But I know it can't work anyways unless I change the name of the column in one of the tables, which would end up with over 60+ pages of editing so it's just going to be easier to make 2 different files that each have their own query and include each on one page. Thank you all for the time anyways

[ginerjm]

There is no reason at all to change the name of a column in any table.  Simply write your query correctly.  Maybe if you do what Barand asked you to do 7 hours ago we can help you.