Jump to content

Php Mysql select from 2 tables


PNewCode

Recommended Posts

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 by PNewCode
Link to comment
Share on other sites

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";

 

Link to comment
Share on other sites

@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>

Link to comment
Share on other sites

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;
Link to comment
Share on other sites

@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 by PNewCode
Link to comment
Share on other sites

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 by ginerjm
Link to comment
Share on other sites

@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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

@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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.