SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: php query error

  1. #1
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    php query error

    Hi I am new to php and I am facing some issues here

    when I am preview the code in the browser the following error come up.

    Database query failed: You have an error in your SQL syntax; check the manual that correspond to your MySWL server version for the right syntax to use near " at line 1

    I am trying to echo a table call pages, It echos the table subjects but then when it comes to pages it shows up the error the coding is the following:

    PHP Code:
    $page_set mysql_query("SELECT * FROM pages WHERE subject_id={$subjects["id"]}",$connection);
    if(!
    $page_set) {
    die(
    "Database query failed:"mysql_error()); 
    There is an obvious error in the query that causes to die instead of retrieving the data from pages.?

    Please help me out.

  2. #2
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you sure that $subjects["id"] has a value? Try echo or print on $subjects["id"] to see what you get.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The error tells you that the sql query you sent to the database is incorrect. You should take a look at the sql query to see what you're sending, so that you can see the problem, which will help you understand what needs to be done to fix it.

    PHP Code:
    echo "SELECT * FROM pages WHERE subject_id={$subjects["id"]}"
    What do you get?

  4. #4
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the values does exist!

    When echo $subjects["id"]; inside the $page_set variable the values of the $subjects [id] don't show up.

    example of the $page_set variable goes as fallows

    PHP Code:
    $page_set mysql_query("SELECT * FROM pages WHERE subject_id={$subjects["id"]}",$connection); if(!$page_set) { die("Database query failed:"mysql_error()); 
    }
    echo 
    "<ul class=\"pages\">";
    while(
    $page mysql_fetch_array($page_set)) {
    echo 
    "<li>{$pages["menu_name"]}</li>";
    // if I echo $subjects["id"]; here it won't show up the subjects id's on the browser. 
    But when I echo $subjects["id"]; inside the $subject_set the values does shows up.


    example of the $subject_set variable which goes as fallows echoing $subjects["id"];
    PHP Code:
    $subject_set mysql_query("SELECT * FROM subjects"$connection);
    if (!
    $subject_set) {
    die(
    "Database query failed: "mysql_error());
    }
    while(
    $subjects mysql_fetch_array($subject_set)) {
    echo 
    "<li>{$subjects["menu_name"]}</li>";
    // if I echo $subjects["id"]; here as you told me then the id's of the subjects will show up in the firefox's Browser

    crmalibu the whole code goes like this:

    PHP Code:
    $subject_set mysql_query("SELECT * FROM subjects"$connection);
    if (!
    $subject_set) {
    die(
    "Database query failed: "mysql_error());
    }
    while(
    $subjects mysql_fetch_array($subject_set)) {
    echo 
    "<li>{$subjects["menu_name"]}</li>";
    }
    $page_set mysql_query("SELECT * FROM pages WHERE subject_id={$subjects["id"]}",$connection); if(!$page_set) { die("Database query failed:"mysql_error()); 
    }
    echo 
    "<ul class=\"pages\">";
    while(
    $page mysql_fetch_array($page_set)) {
    echo 
    "<li>{$pages["menu_name"]}</li>";


  5. #5
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In this particular case, every time you loop through the $subject_set, $subjects gets a new set of values, 1 set for each row of results. It sounds like once the loop is done, your $subjects probably no longer has a value.

    If you are looking for a specific "subject" to call more information on, then you should have a separate variable set to that value. If you need "pages" for all of your subjects, then you'll need to have an array and modify your query accordingly.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cute Tink is right. a while loop will execute as long as the condition is considered true.

    Lets say your SELECT * FROM subjects query resulted in 2 rows.

    PHP Code:
    while($subjects mysql_fetch_array($subject_set)) { 
    echo 
    "<li>{$subjects["menu_name"]}</li>"

    Internally, this is what php is doing
    PHP Code:
    // first row
    $subjects mysql_fetch_array($subject_set);
    if (
    $subjects) {
        echo 
    "<li>{$subjects["menu_name"]}</li>";
    }

    // second row
    $subjects mysql_fetch_array($subject_set);
    if (
    $subjects) {
        echo 
    "<li>{$subjects["menu_name"]}</li>";
    }

    // this time, there is no third row, so mysql_fetch_array() returns false
    $subjects mysql_fetch_array($subject_set);
    // this if statement is false. this is where the loop stops, 
    // but $subjects now holds the value of false from here on out
    if ($subjects) {
        echo 
    "<li>{$subjects["menu_name"]}</li>";


  7. #7
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I kind of undertand what you both are saying

    since the value of subjects will be false so it means I have to set up a new query before $page_set right?

    I would probably need an array and modify a query accordingly.

    Because the results I want to get is getting pages below all of my subjects.

    How can I go about putting an array and modify a query accordingly? that would be before page_set?

    for further information the database I created have two tables one call subjects and one call pages.

    The pages table has three inserts and four fields
    HTML Code:
    id               subject_id                  menu_name            position       visible
    
    1                   1                        History                  1              1
    2                   2                        Our Mission              2              1 
    3                   3                        Smart Pro                1              1


    The subjects table has three inserts and four fields

    HTML Code:
    id             menu_name               position     visible
    
    1               History                  1              1
    2               Our Mission              2              1 
    3               Smart Pro                1              1
    I am trying to get the menu_name of the pages table below the menu_name of the subjects table through the subject_id field and the id field of the subjects table.

  8. #8
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this is what I use:

    PHP Code:
    $array = array();

    $subject_set mysql_query("SELECT * FROM subjects"$connection);

    if (!
    $subject_set) {

    die(
    "Database query failed: "mysql_error());

    }

    while(
    $subjects mysql_fetch_array($subject_set)) {

    $array[] = $subjects["id"];

    echo 
    "<li>{$subjects["menu_name"]}</li>";

    }

    $query "SELECT * FROM pages WHERE subject_id in (" implode','$array ) . ")";

    $page_set mysql_query($query,$connection); if(!$page_set) { die("Database query failed:"mysql_error()); 

    }

    echo 
    "<ul class=\"pages\">";

    while(
    $page mysql_fetch_array($page_set)) {

    echo 
    "<li>{$pages["menu_name"]}</li>";


    Please note where I added $array notations and I altered your "pages" query
    Last edited by Cute Tink; Apr 29, 2009 at 11:27. Reason: Clarification

  9. #9
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey good guys

    I was analizing what you told me about the $subject variable getting a false value after the loop good analises guys:

    PHP Code:
    $subject_set mysql_query("SELECT * FROM subjects"$connection);
     if (!
    $subject_set) { 
    die(
    "Database query failed: "mysql_error()); } 
    while(
    $subjects mysql_fetch_array($subject_set)) { 
    echo 
    "<li>{$subjects["menu_name"]}</li>"
    $page_set mysql_query("SELECT * FROM pages WHERE subject_id={$subjects["id"]}",$connection); 
    if(!
    $page_set) { 
    die(
    "Database query failed:"mysql_error()); } 
    echo 
    "<ul class=\"pages\">"; while($page mysql_fetch_array($page_set)) { echo "<li>{$pages["menu_name"]}</li>"; } 
    if you take a look at the Curly Braces before page_set which was causing the value of subjects to return to false again once i put that Curly Brace out it worked. It retrieved the information out of the Database and put the pages belows the subjects thanks to the ul and li tags.

    I will try the way you gave me, I will create a query variable and put it together to try the array and query modification you did.


    Thank you guys I have started to dive in into php Language I am very happy.

  10. #10
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    your implode version works good it display the data, but the only thing is that it is not displaying each page in its respective subject it first display the three subjects and then a loop of the three pages.

    instead of subject-page, subject-page, subject-page how can we fix it to fit in this format or the array version.

    In the verstion we fixed before the pages were able to show up after each subject because I took out the Curly Brace i was talking about. When i take them out in your version the browser shows up a Parse error on line 40 but line 40 is the last one I don't know why the browser refers to the last line.

  11. #11
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can store the results in an associative array and echo them where you want them. You will have to hold off echo-ing the results of the queries until you have all of the information that you need.

  12. #12
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And how do you place an associative array and hold the echo with the array and modification query you did?

  13. #13
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using the same snippet of code:

    PHP Code:
    $array $assoc = array();

    $subject_set mysql_query("SELECT * FROM subjects"$connection);

    if (!
    $subject_set) {

    die(
    "Database query failed: "mysql_error());

    }

    while(
    $subjects mysql_fetch_array($subject_set)) {

    $array[] = $subjects["id"];

    $assoc[$subjects['id']] = $subjects['menu_name'];

    }

    $query "SELECT * FROM pages WHERE subject_id in (" implode','$array ) . ")";

    $page_set mysql_query($query,$connection); if(!$page_set) { die("Database query failed:"mysql_error()); 

    }

    while(
    $page mysql_fetch_array($page_set)) {

    echo 
    "<li>{$assoc[$page["subject_id"]]}</li>
           <ul class=\"pages\">
           <li>
    {$pages["menu_name"]}</li>
           </ul>"
    ;


    I didn't test this, so you may want to modify the output to see how things look.


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
  •