Need help with normalisation.

[ApacheTech]

I hate MySQL!

I've now got as far as:

<?php

$links = $db->query('SELECT L.*, C.name AS category FROM links AS L INNER JOIN categories AS C ON C.id = L.category_id');

foreach($links as $link) {
    array_push($link['websites'], $db->query("SELECT * FROM websites WHERE `table` IS 'links' AND `record_id` = " . $link['id'])); 
    array_push($link['adverts' ], $db->query("SELECT * FROM adverts  WHERE `table` IS 'links' AND `record_id` = " . $link['id'])); 
}

?>

I have no idea how to do the WHERE clause.

It gives the error:

Fatal error: Problem preparing query (SELECT * FROM websites WHERE `table` IS 'links' AND `record_id` = 1) 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 ''links' AND `record_id` = 1' at line 1

[james438]

I've been looking at your syntax and I am having trouble seeing any errors. Could it be your use of double quotes? Normally this is not an issue, but it may be if used with some php statements that also use quotes.

[james438]

I posted too late.

[ApacheTech]

I've finally got it working, but Valhalla knows why it wasn't.

I've used the code:

<?php

$links = $db->query('SELECT L.*, C.name AS category FROM links AS L INNER JOIN categories AS C ON C.id = L.category_id');

foreach($links as $id=>$link) {
    $websites = $db->where('table_ref',$_SESSION['PAGE'])->where('record_id',$link['id'])->get('websites');
    $adverts = $db->where('table_ref',$_SESSION['PAGE'])->where('record_id',$link['id'])->get('adverts');
    $links[$id]['websites'] = array();
    $links[$id]['adverts'] = array();

    foreach($websites as $key=>$value) {
        array_push($links[$id]['websites'], $websites[$key]['url']);
    }

    foreach($adverts as $key=>$name) {
        array_push($links[$id]['adverts'], $websites[$key]['name']);
    }
}

?>

It's not throwing up any errors and the var_dump seems ok. It's annoying though that I couldn't use plain SQL for it, I've had to use the MySQLiDB. I was hoping to learn SQL by doing this.