Page 3 of 3 FirstFirst 123
Results 21 to 24 of 24

Thread: Need help with normalisation.

  1. #21
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

    I hate MySQL!

    I've now got as far as:

    PHP 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 $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

  2. #22
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,696
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    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.
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #23
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,696
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    I posted too late.
    To choose the lesser of two evils is still to choose evil. My personal site

  4. #24
    Join Date
    Apr 2012
    Location
    Chester, Cheshire
    Posts
    329
    Thanks
    7
    Thanked 35 Times in 35 Posts

    Default

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

    I've used the code:

    PHP 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.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •