MYSQL Help Please

Databases
1

I have a database, it has two tables:

users & writing_stats

There are columns called user_id & user_name in the users table (along with a number of other columns)

In writing stats there is a table called user_id (and a number of other columns).

I want to be able to retrieve the user_id in writing_stats and have it also return user_name in the same query.

Is this possible?

2

yes, you would need to join the two tables on primary / foreign key. Also, you can introduce filtering by adding conditional statements after the join.

example:

Select a.user_id, a.user_name, b.writing_stats
from T_TABLE_1 as a
join T_TABLE_2 as b on b.user_id = a.user_id
3

I will give it a try Rich, thanks for the help!

4

so used to MSSQL, forgot to use as in the table aliases in which MySQL requires. I modified my query above.

5

Rich, I am not getting an error, but I am also not getting a return, Here is the code:

$con = mysqli_connect(“localhost”,“UserName”,“Password”,“DatabaseNamel”) or die(“Not connected.”);

$sql = ‘Select a.user_id, a.user_name, b.writing_stats from users as a join writing_stats as b on b.user_id = a.user_id WHERE writing_date = CURRENT_DATE ORDER BY word_count DESC’;
$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_array($result)) {

echo "USER ID : {$row[‘user_id’]}
".
"USER NAME: {$row[user_name]}
".
"WORD_COUNT : {$row[‘word_count’]}
".
“--------------------------------
”;
}

6

The sql inside your $sql variable does not require the apostrophes, and they look like the wrong type of apostrophes as well. Try removing them and run again. There also appears to be a stray semicolon at the end.

7

I took out the apostrophes, the semi-colon is required by php.

No return of data still.

10

Further:

while($row = mysqli_fetch_array($result)) {
echo $sql;
}

Doesn’t return anything as well. (though I do get a full result set in phpmyadmin)

11

This returns the proper result set (in phpmyadmin):

select * from users, writing_stats WHERE users.user_id = writing_stats.user_id AND writing_date = CURRENT_DATE ORDER BY word_count DESC

But my php is not displaying the result set (shows blank).

while($row = mysqli_fetch_array($result)) {

echo "USER ID : {$row[‘writing_stats.user_id’]}
".
"USER NAME: {$row[‘users.user_name’]}
".
"WORD_COUNT : {$row[‘writing_stats.word_count’]}
".
“--------------------------------
”;
}

12

Just for anyone following this thread and looking for the answer:

$result = mysqli_query($con,$sql);

while ($row = mysqli_fetch_array($result)) {

echo $row[‘user_name’] . $row[‘word_count’] ;

}

Fixed the problem, I had left off the $row from the return.