1

My first two while loop are working now but when i add the 3 one for the following level i get another error :

Database query failed: 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 '' at line 1

Level 1 - Works

    $parentpage_set = mysql_query("SELECT * FROM parentpages", $connection);
    if (!$parentpage_set) {die("Database query failed: " . mysql_error()); }
    echo "<ul class=\"subjects\">";
    while ($parentpage = mysql_fetch_array($parentpage_set)) {echo "<li>{$parentpage["menu_name"]}</li>";
    echo "</ul>";

Level 2 - works 

    $childpage_set = mysql_query("SELECT * FROM childpages WHERE parentpage_id = {$parentpage["id"]}",$connection);
    if (!$childpage_set) {die("Database query failed: " . mysql_error()); }
    echo "<ul class=\"pages\">";
    while ($childpage = mysql_fetch_array($childpage_set)) {echo "<li>{$childpage["menu_name"]}</li>"; }
    echo "</ul>";

Level 3 - not working if i comment this out my query runs but when un-commented i receive an error 

    $subchildpage1_set = mysql_query("SELECT * FROM subchildpages1 WHERE childpage_id = {$childpage["id"]}",$connection);
    if (!$subchildpage1_set) {die("Database query failed: " . mysql_error()); }
    echo "<ul class=\pages\">";
    while ($subchildpage1 = mysql_fetch_array($subchildpage1_set)) {echo "<li>{$subchildpage1["menu_name"]}</li>"; }
    echo "</ul>";

i still have to add two more levels to my navigation - i can not pick up my mistake and i have been trying to pick it up and i have read thru a lot of questions, can some one please tell me where my mistake is please

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
Hanna
  • 11
  • 4
  • 1
    Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Jan 14 '16 at 13:11
  • 1
    If you echo out the query what do you see? That is the first thing you should do when looking for syntax errors in queries. – Jay Blanchard Jan 14 '16 at 13:13
  • Do yourself a massive favour and do some basic seperation of concerns. At the very least, perform the database queries at the top of the page, save the results into the structure you require (some form of array), then iterate the array inside the html. Mixing complex php with html is always a bad idea - just trying to read the above code is giving me a headache - no wonder you are struggling – Steve Jan 14 '16 at 13:16
  • thank you for your help, my firs parentpage menu name appers 1. Scope and the the error Database query failed: 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 '' at line 1 that is all that the echo comes back with, i m very new to the programing word and this is my very first coding try – Hanna Jan 14 '16 at 13:19
  • print $connection above the first level and $connection after the second level. There is a chance that mysql can close the connection. – akhilp2255 Jan 14 '16 at 13:20
  • @akhilp2255 i have require_once("includes/connection.php"); before every level and still the same error. the error only comes in one i try to add the 3rd while loop. – Hanna Jan 14 '16 at 13:39
  • @Steve i have done the database connection in the same file and it connect to the data base still the same error , the error only comes in one i try to add the 3rd while loop. – Hanna Jan 14 '16 at 13:39
  • Please check the value inside `$childpage["id"]` – akhilp2255 Jan 15 '16 at 05:28
  • @akhilp2255 - i have added the code mentioned in the answe and i got this string(50) "SELECT * FROM subchildpages1 WHERE childpage_id = " NULL-this is my table construction 1, id, int(11), No, None, AUTO_INCREMENT: 2, childpage_id, int(11), No, None: 3, menu_name, varchar(70),latin1_swedish_ci, No, None: 4, position, int(3), No, None: 5, visible, tinyint, (1), No, None: 6, content, text, latin1_swedish_ci, No, None: and my table content is not NULL aether id, childpage_id, menu_name, position, visible, content: 1, 1, 4.1.1 Review & Index, 1, 1, 0: 2, 1, 4.1.2 Guidelines & Standards, 2, 1, 0 – Hanna Jan 15 '16 at 08:37

1 Answers1

0

What you need here is to debug.

Debugging in PHP can be quite easy for this kind of problems.

You've got a syntax error in your query. Looking at the query I could guess there are two options for errors. First one is misspelling some of the key words in the query. Second one is that $childpage["id"] has an empty value. In such case the build query will result in:

"SELECT * FROM subchildpages1 WHERE childpage_id ="

instead of

"SELECT * FROM subchildpages1 WHERE childpage_id = 1"

for example.

So what you can do here is to assign the query in a variable and print it. And considering the assumptions for the two possibilities for error also print the $childpage["id"] variable.

When debugging variables always use var_dump() or print_r().

Example:

$sql = "SELECT * FROM subchildpages1 WHERE childpage_id = {$childpage["id"]}";

var_dump($sql, $childpage["id"]);
die();

$subchildpage1_set = mysql_query($sql, $connection);

If $childpage["id"] is NULL or empty string - "", then you should check why is that.

Pavel Petrov
  • 847
  • 10
  • 19
  • i have added the code above and i got this string(50) "SELECT * FROM subchildpages1 WHERE childpage_id = " NULL this is my table construction 1, id, int(11), No, None, AUTO_INCREMENT: 2, childpage_id, int(11), No, None: 3, menu_name, varchar(70),latin1_swedish_ci, No, None: 4, position, int(3), No, None: 5, visible, tinyint, (1), No, None: 6, content, text, latin1_swedish_ci, No, None: and my table content is not NULL aether id, childpage_id, menu_name, position, visible, content: 1, 1, 4.1.1 Review & Index, 1, 1, 0: 2, 1, 4.1.2 Guidelines & Standards, 2, 1, 0: – Hanna Jan 15 '16 at 06:22
  • the table is not empty and i am not sure as to why php receive a Null value - i have even drop both table and recreated them, no change, i have login to mysql via command prompt and Select * from ..... both my tables and mysql has retured a value but php still wont pick up my value, please help – Hanna Jan 15 '16 at 08:35
  • Sorry i do not understand clearly, parentpages is my main menu with it id to each "page", childpages is my next level(2) in my menu an the parentpage_id links it to the parentpage, subchildpages1 is my 3de level menu childpage_id links it to the level 2 menu. it seems that my variables subchildpages1(subchildpages2, subchildpages3) is not setting or is getting a Null value from mysql in have loged it to my sql via cmd and pulled the tables there all has the intended value. all tables are unlocked is there a better way for me to do my navigation witch a can rawurlencode aswell? – Hanna Jan 15 '16 at 11:29
  • Your where clause for childpages table is `parentpage_id = {$parentpage["id"]}`.. Your childpages table may not be empty but there may be no records with parnetpage_id with same value as $parentpage["id"]. Check the the value $parentpage["id"] – Pavel Petrov Jan 15 '16 at 11:32
  • i have in childpages - id menu_name 1 4.1. General Requirements 2 4.2. OH&S Policy 3 4.3. Planning 4 4.4. Implementation & Operation 5 4.5. Checking 6 4.6. Management Review and in subchildpages1 childpage_id menu_name position 1 4.1.1 Review & Index 1 1 4.1.2 Guidelines & Standards 2 3 4.3.1 Hazard Identification & Risk Assessments - Determining Controls 1 3 4.3.2 Legal & Other Requirements 2 so 4.1. General Requirements(id 1) two sub pages will be 4.1.1 Review ..(childpage_id 1) and 4.1.2 Guidelines.. – Hanna Jan 15 '16 at 12:09
  • if i remove the WHERE statement i get my values but not under the right childpages so i do believe my problem is definitely there where statement but i am not sure how to fix it, and i don't understand why did the level2 code work but not my level3 and onwards – Hanna Jan 15 '16 at 12:13