Help with query

[singingsands]

This is a simple query that for some reason that I'm missing is throwing an error.

<?php
session_start();
if (!isset($_SESSION['Weekend_Number'])) {
   header("Location: add_team.php");
   exit;
}

$Number=$_SESSION['Weekend_Number'];
$MW=$_SESSION['Men_Women'];

$query =  mysqli_query ($conn, "Select * FROM weekends WHERE Weekend_Number = $Number AND Men_Women = $MW");

..../
?>

The error I get is 

Fatal error: Uncaught mysqli_sql_exception: Unknown column '55th' in 'where clause' in C:\xampp\htdocs\cursillo\add_team_details.php:16 Stack trace: #0 C:\xampp\htdocs\cursillo\add_team_details.php(16): mysqli_query(Object(mysqli), 'Select Weekend_...') #1 {main} thrown in C:\xampp\htdocs\cursillo\add_team_details.php on line 16

Apparently the variable $Number is being taken for the column name instead of the field in the column. If I put the same query into the mysql database it works fine. Am I missing something?

Note: Weekend_Number is not the ID field.

Edited yesterday at 12:39 PM by singingsands

[maxxd]

There are issues here beyond the error you're seeing. First and foremost, drop mysqli_* and use PDO - it's easier to use and can handle several different SQL dialects. Secondly, never put raw user data into a query (session data can be modified by the user). Use prepared statements in order to not lose or expose your data. As to the actual issue you're seeing, print out the value of $Number and $MW before you run the query to make sure they contain what you think they contain. If the value is actually '55th' you need quotes around the value - another bonus of using prepared statements (preparing the statement will take care of that for you).

[mac_gyver]

i second the use of prepared statements. you MUST protect against sql special characters in a value being able to break the sql query syntax, which is how sql injection is accomplished. a prepared query is the simplest (provided you use the much simpler and better designed PDO extension), fool proof way of accomplishing this for all data types.

also, if the 'th' (ordinal indicator) is actually part of the value, it should not be. this is a human convention and should only be displayed, not actually submitted or stored. you should only submit or store the integer value.

the code copying session variables to other variables is both unnecessary (just use the original variable that data is in) and indicates that your form and form processing code is on different pages. by separating these, you are nearly doubling the amount of code and since it takes more code to accomplish a task, you are likely leaving out things that provide security or a good User eXperience (UX.) the code for any page should be laid out in this general order -

1. initialization
2. post method form processing
3. get method business logic - get/produce data needed to display the page
4. html document

[singingsands]

I appreciate your advice about prepared statements, I'll look into it further.  I have absolutely no experience with PDO but I'll check it out too.

As for my particular problem, I have echoed the variables to make sure they are correct and they are.  I've put quotes on the variables and it makes no difference to the problem. And as I mentioned, I've run the query directly in the database using the values of the variables and it works fine. 

[mac_gyver]

you should use var_dump() on the values for debugging, since it indicates the length of the value. unfortunately, you didn't show us what you saw when you echoed the variables and if you made a change to the sql query statement and it didn't work, showing us what you changed would help someone solve the problem.

converting a query that has variables being put directly into it into a prepared query is straight forward -

1. remove, and keep for later, any php variables that are inside the sql query statement. note: any wild-card characters in a LIKE comparison are supplied as part of the data value not as part of the sql query statement.
2. remove any quotes or {} that were around the php variable and any concatenation dots/extra quotes that were used to get the php variable into the sql query statement.
3. put a simple ? prepared query place-holder into the sql query statement for each value.
4. call the PDO prepare() method for the sql query statement. this returns a PDOStatement object.
5. call the PDOStatement execute([...]) method with an array of the variables you removed in step #1.

for a query that returns a result set, fetch the data from the query. see the PDOStatement fetch() method when fetching a single row of data. the  PDOStatement fetchAll() method when fetching all the rows of data at once. and occasionally the PDOStatement fetchColum() method when fetching a single column from a single row of data. forget about any num rows function/method/property. just fetch then test if/how many rows of data there are.

for a query that doesn't return a result set, you can use the PDO lastInsertId() method and the PDOStatement rowCount() method to get the last insert id and the number of affected rows.

for the query in this thread, this would look like -

// i recommend that you build the sql query statement in a php variable. this makes debugging easier since you can echo the sql
$sql = "Select * FROM weekends WHERE Weekend_Number = ? AND Men_Women = ?";

$stmt = $pdo->prepare($sql);
$stmt->execute([ $_SESSION['Weekend_Number'], $_SESSION['Men_Women'] ]);

// if this query can match a set of data
$result = $stmt->fetchAll();
// if this query can match at most one row of data
$result = $stmt->fetch();

typical PDO connection code -

$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions - this is the default setting now in php8+
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // set default fetch mode to assoc
			];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

 

[maxxd]

mac_gyver is 100% correct with those suggestions. I will add that using '?' placeholders can get confusing if you've got several to many variables in your query - in this case i recommend named placeholders. So, to update mac_gyver's perfectly good code as an example,

$sql = "Select * FROM weekends WHERE Weekend_Number = :weekendNumber AND Men_Women = :menWomen";

$stmt = $pdo->prepare($sql);
$stmt->execute([
    'weekendNumber' =>  $_SESSION['Weekend_Number'],
    'menWomen' => $_SESSION['Men_Women']
]);

Note that another benefit of using PDO over mysqli is that you don't have to bind the parameters separately. It's been a while since I used mysqli, but i think i remember having to bind the result values as well? If I'm remembering correctly, this is another thing you don't have to do with PDO.