CODEWITHSUNDEEP
phpmysqlselect
user652792
user652792

Reputation:

is my select statement right

Is this statement correct syntactically:

$query = "SELECT matric_no session course_name
              FROM main_table session
              WHERE main_table.matric_no = '$_POST[matric_no]'
                  AND session.session_name = '$_SESSION[session]'
                  AND session.course_name =  '$_SESSION[course_name]'";
$result = mysql_query($query) ;                       
$duplicates = mysql_num_rows($result);

if ($duplicates = 1) {
    echo "you have dis in the database";
};

Upvotes: 0

Views: 89

Answers (5)

hafichuk
hafichuk

Reputation: 10781

One thing that you need to seriously reconsider is using unsanitized data from $_POST and $_SESSION. This is potentially a major security risk to your site as someone can fairly easily perform some SQL Injection.

At a minimum you should be using mysql_real_escape_string().

I would also look at migrating your code to use mysqli or PDO, as mysql is quite outdated.

Upvotes: 0

Sir Wally
Sir Wally

Reputation: 21

There are three problems:
1) Missing comma (already pointed out) in the select statement.
2) With the first problem solved it should then generate a Cartesian product because you didn't join between the two tables. You should have something like this:

SELECT
  m.matric_no,
  s.course_name
FROM
  main_table mt,
  session s
WHERE
      mt.[SOMECOLUMN] = s.[ANOTHERCOLUMN]
  AND mt.matric_no    = '$_POST[matric_no]'
  AND s.session_name  = '$_SESSION[session]'
  AND s.course_name   = '$_SESSION[course_name]'"

or (if ANSI-style joins are supported) this:

SELECT
  m.matric_no,
  s.course_name
FROM
  main_table mt
  JOIN
    session s ON (mt.[SOMECOLUMN] = s.[ANOTHERCOLUMN])
WHERE
      mt.matric_no   = '$_POST[matric_no]'
  AND s.session_name = '$_SESSION[session]'
  AND s.course_name  = '$_SESSION[course_name]'"

3) The query is open to a SQL injection attack. NEVER pass variables in without first cleansing them (at least variables that can be altered by the user). If at all possible use query parameters to take care of the issue. If this is not an option, simply cleanse the variables by removing characters such as single-quotes that can be used to alter the query. It has been a while since I have used MySQL, so maybe there is some magic I am unaware of that will take care of this for you, but I don't see how that would be possible. UPDATE: Do what hafichuk suggests above :-)

Upvotes: 2

pelelive
pelelive

Reputation: 637

you don't need all the info in the SELECT to check if session is in database.

   $matricNo = $_POST[matric_no];
    $sessionName = $_ SESSION[session];
    $courseName = $_ SESSION[course_name];

//here you should use mysql_real_escape_string() for security
//like this $matricNo = mysql_real_escape_string($matricNo);

$query = "SELECT m.matric_no
          FROM main_table as m, session as s
          WHERE m.matric_no = '$matricNo' 
            AND s.session_name = '$sessionName'
            AND s.course_name = '$courseName'"; 

$result = mysql_query($query) or die (mysql_error()); 

$duplicates = mysql_num_rows($result); 

if ($duplicates > 0) { 
    echo "you have dis in the database"; 
};

Upvotes: 2

gprathour
gprathour

Reputation: 15333

there are some syntax problems in your query

if main_table and session are two different tables then it should be something like this.

"SELECT main_table.matric_no, session.course_name
          FROM main_table, session
          WHERE main_table.matric_no = '$_POST[matric_no]'
              AND session.session_name = '$_SESSION[session]'
              AND session.course_name =  '$_SESSION[course_name]'"

Upvotes: 0

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100205

Shouldnt it be:

$query = "SELECT main_table.matric_no, session.course_name

Hope it helps

Upvotes: 0

Related Questions