-2
$level_sql_switch = mysqli_query($conn, "SELECT course_period_id 
                                         FROM course_periods 
                                         WHERE school_id = '$campus' 
                                         AND short_name = '$level'");
$course_period_id = $level_sql_switch->fetch_assoc();
$course_period_id = $course_period_id['short_name'];

For Example, when the excel file is read, the variable $level stores the value "C1". I would like to return a C1-T from the database using a select statement. How would you do so while ruling out the other variants of C1 (C1-A, C1-B, C1-C, C1-T2 and etc). As you cannot simply set the short_name = '$level' or use a substring.

"SELECT course_period_id FROM course_periods WHERE school_id = '$campus' AND short_name = '$level'"
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Xenos
  • 19
  • 5
  • 2
    `mysqli_qeury()` is that a custom function of yours? If not; it's a typo. – Funk Forty Niner Aug 30 '18 at 15:06
  • *"when the short_name being read from the excel is C1"* - Excel? Your question's unclear for a few reasons here. – Funk Forty Niner Aug 30 '18 at 15:07
  • It is not a custom function, has it been depreciated?https://www.w3schools.com/php/func_mysqli_query.asp – Xenos Aug 30 '18 at 15:08
  • 3
    ___Not deprecated, but you have to spell it correctly___. Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any mysqli_ errors to generate an Exception that you can see on the browser as well as normal PHP errors. – RiggsFolly Aug 30 '18 at 15:08
  • 1
    No, you wrote qeury instead of query – Emaro Aug 30 '18 at 15:08
  • No its misspelled its `query` as you wrote yourself not `qeury` as it is in your code – Manav Aug 30 '18 at 15:09
  • 2
    Also "depreciated" and "deprecated" are two very different things. Check your spellings generally, I think. – ADyson Aug 30 '18 at 15:10
  • 1
    Hahaha rough day – Xenos Aug 30 '18 at 15:12
  • Possible duplicate of [PHP - concatenate or directly insert variables in string](https://stackoverflow.com/questions/5605965/php-concatenate-or-directly-insert-variables-in-string) – MonkeyZeus Aug 30 '18 at 15:14
  • Thanks for pointing that out riggsfolly actually helps alot! – Xenos Aug 30 '18 at 15:16
  • Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Aug 30 '18 at 16:58
  • 1
    @Xenos Try to avoid w3schools whenever possible. That site contains wildly out of date material and is chock full with toxically bad advice. The [official documentation](http://php.net/manual/en/book.mysqli.php) is considerably better and the community comments help in situations where the documentation isn't entirely clear. Another good resource if you're just getting started is [PHP the Right Way](https://www.phptherightway.com). – tadman Aug 30 '18 at 16:59

1 Answers1

1

It's not 100% made clear, but presumably $level won't always contain C1. Assuming also that you always want to find the variant -T then you could simply set the parameter as

short_name = '$level-T'

In full:

SELECT course_period_id 
FROM course_periods 
WHERE school_id = '$campus' AND short_name = '$level-T'

It should also be noted that your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. Never insert unsanitised data directly into your SQL like the above.

As I said above, you can find examples of precisely how to write what you need, but a parameterised version of your query with mysqli would look like:

SELECT course_period_id 
FROM course_periods 
WHERE school_id = ? AND short_name = ?

Then elsewhere you'd assign your first parameter to be $campus and your second to be "$level-T" before passing them to the query.

ADyson
  • 57,178
  • 14
  • 51
  • 63
  • Why is this concatenation even remotely necessary? PHP can interpolate like it does for `$campus`. That being said, both of these should be placeholder values. – tadman Aug 30 '18 at 16:58
  • @tadman true. Not sure why I did that. Have edited. And yes it should really be parameterised, but that's a separate topic I guess. I don't know if the values in question are taken from user input into the spreadsheet or not. It's not clear. – ADyson Aug 30 '18 at 21:27
  • Getting it working *correctly* is never a separate topic. This is half a fix, and it preserves the huge SQL injection hole. Parameterizing it is easy for you, just put `?` instead and leave the rest as an exercise for the reader. You'll also need to use double-quotes on `$short_name = "$level-T"` or it won't interpolate. – tadman Aug 30 '18 at 21:28
  • 1
    Improvements much appreciated, though it's usually best to give the safe advice first. People usually latch on to the first thing that "works" and don't read the warnings. – tadman Aug 31 '18 at 17:14