mysql error on re-load - perhaps pass values through hyperlink

[davidcriniti]

Hi everyone,

 

I was given some great advice by Denno020 on how to display content in the main area of my table based on a link clicked on the side.

 

<a href="course_display.php?link=1">Link 1</a>


<?php
if(isset($_GET['link']) && $_GET['link'] == '1'){
echo "Show if and when Link 1 is Clicked.....";
}
?>

 

 

It works perfectly under normal circumstances. However, I'm trying to apply it to a page (course_display.php) which is reached by a form (course_search.php) which asks users to supply an id (course_id) and password (course_password)

 

- The course_display.php page loads great the first time when reached via course_search.php

- If I hit the refresh button in my browser, it refreshes perfectly.

 

However, when I click on the link (as in the code above), I get an error which says:

 

Error retrieving info from database!

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 'AND course_password=''' at line 1

 

I'm not sure why if there was an SQL syntax error it wouldn't show the first time the page is loaded?

 

I suspect the problem is either solved by:

 

- Fixing mysql syntax

OR

- passing the variables $course_id and $course_password through the hyperlink in addition to the variable 'link=1'

OR

- both of the above.

 

The mysql syntax is:

 

include "mysql_connect.php";


  $course_id = $_POST['course_id'];


   // The basic SELECT statement
$select = 'SELECT *';
$from   = ' FROM tbl_course_titles';
$where  = ' WHERE course_id =' . $course_id . '';
$order = 'ORDER BY id ASC';



$course_password = $_POST['course_password'];


 $where .= " AND course_password='$course_password'";



$result1 = @mysql_query($select . $from . $where);
if (!$result1) {


 exit('<p>Error retrieving info from database!<br />'.
     'Error: ' . mysql_error() . '</p>');
}


$num_rows = mysql_num_rows($result1);
if ($num_rows == 0){
 echo "Sorry, your Course ID / Password combination is incorrect, go back and try again";
 die();
}

 

 

 

...I've tried 2 ways to pass multiple variables through the hyperlink. Not sure if either of these are correct?

 

  echo   "<a href=\"course_display.php?link=2?course_id= ". $course_id. "?course_password = " . $course_password . " \">Instructions</a><br />";



echo  "<a href=\"course_display.php?link=1?course_id=$course_id?course_password=$course_password \">Instructions2</a><br/>"; 

 

 

Any advice on how to solve this problem is greatly appreciated.

 

Cheers,

 

Dave

[exeTrix]

The structure of your link is incorrect. Should be:

 

echo   "<a href=\"course_display.php?link=2&course_id=". $course_id. "&course_password=" . $course_password . " \">Instructions</a><br />";

 

When constructing a URL you need to have the page, in this case course_display.php then a question mark to start the get key value pairs so now we have course_display.php? next add the key value pairs with no spaces course_display.php?link=2&course_id=1&......

[AyKay47]

A side note, you need to be mysql_real_escape_stringing all $_POST data before using it in a query to prevent SQL injection.

It seems silly to append variables together like that to create a query, especially when some of them contain static data.

A common query debugging method is to output both the mysql_error() and the query itself to check for errors:

 

$sql = "select * from example";
$resource = mysql_query($sql) or die("Query: " . $sql . "<br>Error: " . mysql_error());

 

the above is pseudo code.

[davidcriniti]

Hi,

 

I've added mysql escape string and put the query together in a manner you suggested.

 

include "mysql_connect.php";
$course_id =    mysql_real_escape_string ($_POST["course_id"]);

$course_password = mysql_real_escape_string ($_POST['course_password']);
   // The basic SELECT statement
$select = 'SELECT * FROM tbl_course_titles WHERE course_id =' . $course_id  . ' AND course_password = ' . $course_password . '';

$result1 = mysql_query($select)
or die("Query: " . $select . "<br>Error: " . mysql_error());

 

When I input correct details into the searchcourses.php form, I now get:

 

Query: SELECT * FROM tbl_course_titles WHERE course_id =1 AND course_password = secret

Error: Unknown column 'secret' in 'where clause'

 

My course id is 1 and passowrd is secret.

 

For some reason it seems to think 'secret' is a column.

 

I've tried a bunch of different variants and googling and I am feeling quite silly right now, but any advice would be much welcome.

 

Thanks,

Dave

[AyKay47]

This is because you are not wrapping the course_password value in quotes in the SQL statement, so MYSQL thinks you are referring to another column.

Change the SQL statement to:

 

$select = "SELECT * FROM tbl_course_titles WHERE course_id = $course_id AND course_password = '$course_password'";

Edited February 23, 2013 by AyKay47

[davidcriniti]

Thank you. I've made great progress on this page.

 

I've altered my $select statement as per your advice AyKay47. The page loads perfectly the first time.

 

However, when I click on the link:

 

 

...I get a message which says :

 

echo "<a href=\"course_display.php?link=2&course_id=". $course_id. "&course_password=" . $course_password . " \">Instructions</a><br />";

 

 

.. I still get an error which says:

 

Query: SELECT * FROM tbl_course_titles WHERE course_id = AND course_password = ''

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 'AND course_password = ''' at line 1

 

Any ideas?

 

Thanks again,

 

Dave

ps: Edit - mistake when pasting in code.

Edited February 23, 2013 by davidcriniti

[AyKay47]

It does not look like $course_id and $course_password contain any values, are you retrieving them via $_GET?