PHP mySQL, Joining More than Two Tables Together with Similar IDs?

Question 1

Here is what I am working with:

$query = "SELECT Products.Title, Product_Lines.pl_Title, Manufacturers.man_Title".
 "FROM Products, Product_Lines, Manufacturers ".
    "WHERE Products.pl_ID = Product_Lines.pl_ID AND Product_Lines.man_ID = Manufacturers.man_ID";

$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
    echo $row['Title']. " - ". $row['pl_Title']. " - ". $row['man_Title'];
    echo "<br />";
}

I am getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'WHERE Products.pl_ID = Product_Lines.pl_ID AND  
Product_Lines.man_ID = Manufactur' at line 1

I am unfamiliar with this method and this error

Question 2

SELECT Products.Title, Product_Lines.pl_Title, Manufacturers.man_Title
 FROM Products INNER JOIN Product_Lines ON Products.pl_ID = Product_Lines.pl_ID INNER JOIN Manufacturers ON Product_Lines.man_ID = Manufacturers.man_ID

WILL DO

Question 3

Use GROUP_CONCAT. try this one,

SELECT  a.name, GROUP_CONCAT(CONCAT(firstname, ' ', surname))
FROM    course a
        INNER JOIN courses_has_teachers b
            ON a.id = b.course_id
        INNER JOIN teacher c
            ON b.teacher_id = c.iD
GROUP BY a.name

Question 4

Create a script that generates a config file (e.g. config.php) with the database connection variables. Don't forget to set the right file-permissions after you done with a new config file.

Update:

$config .= "<?phpn"; 
$config .= "$database = '".$_POST['database ']."';n";
$config .= "$user= '".$_POST['user']."';n";
$config .= "$pass= '".$_POST['pass']."';n"; 
$config .= "?>n"; 

file_put_contents("config.php", $config);

Question 5

The query you want is probably this one:

select n.notification_id, u.user_id
  from notifications n
    cross join users u
    left join notifications_log nl
      on n.notification_id = nl.notification_id
        and nl.user_id = u.user_id
   where nl.notification_log_id is null

demo here

This query eliminates your derived table, reducing the execution time, and performs the cross join as early as possible to reduce the total number of rows being operating on.

But i'd suggest rethinking this altogether. Once notifications and users table reaches critical mass this is going to create millions upon millions of rows to filter.

A better idea would be to have a notification_inbox table, as a counterpart to your notifications_log table. When a notification is created, place it in the inbox table for each user. That way you can perform a simple query on a single table to determine unread notifications per user, rather than a potentially horrendously performing cross join.

Alternatively again, a single notification_delivery table, rather than inbox and log tables, which has a 'read' flag. This would also allow targeted notifications, as well as bulk delivery to all users.

Question 6

Those are idle connections being held by a client. You should make sure that whatever client library you are using (JDBC, ...) is configured to not keep unused connections open so long, or that your # clients * max # of connections isn't too big.

PHP mySQL, Joining More than Two Tables Together with Similar IDs?

Answers

php,mysql,mysql-management