Similar queries

[ajoo]

Hi all,

 

Is there a better, shorter, sweeter way to pull out the data from the two similar set of queries below? 

$staff_id = '34';

if($staff > 0)
{
   $query = "SELECT fname, lname, city, email, cell FROM staff";
   $stmt = $con->prepare($query);
   $stmt->execute();
   $stmt->bind_result($fname, $lname, $city, $email, $cell);
   $stmt->store_result();
   $stmt->close();
}
else
{
   $query = "SELECT fname, lname, city, email, cell FROM staff WHERE staff_id=?";
   $stmt = $con->prepare($query);
   $stmt->bind_param('i',$staff_id);
   $stmt->execute();
   $stmt->bind_result($fname, $lname, $city, $email, $cell);
   $stmt->store_result();
   $stmt->close();
}

Thanks !

 

[requinix]

Don't use a prepared statement.

$query = "SELECT fname, lname, city, email, cell FROM staff" . ($staff > 0 ? " WHERE staff_id = " . (int)$staff : "");

But what you're doing doesn't make sense. Without a staff ID, you'll pull just one random person's information out. What's the point?

[mac_gyver]

it's not clear what you are asking, since we don't know what your 'business rules' are that determine which query to run or what result you expect.

 

however, i suspect you are asking how can you form and run any arbitrary SELECT query without writing and repeating different variations of the php logic depending on what the query is? if so, you need to write a general purpose query method, that you extend the main database class with, that accepts a built up sql query statement and an optional array of bound input data that corresponds to any place-holders in the sql statement. your main application code would build the sql query statement and the array of bound input data, then just call the general purpose query method when it needs to execute a query.

 

unfortunately, you shouldn't use msyqli to do this, since it will require dynamically binding the input and the output data, which will require either using call_user_func_array() or the reflection class, since you must supply a variable number of parameters to the mysqli bind_param() and bind_result() calls.

 

if you instead use the PDO extension, doing this is simple, straight forward, and will run fun significantly faster than using msyqli based code.

[ajoo]

Hi requinix, 

 

Thanks for the reply and this method. 

 

Without the staff_id, it will print out all the records in the table.

 

Another thing,  is this method security safe ? 

 

Thanks loads !

[ajoo]

Hi mac_gyver,

 

Thanks for your reply. Yes you interpreted my question right. The code example is arbitrary and the idea is to reduce the code size. So yes that's it.

 

I am using procedural code without classes and I am using mysqli. So is there any way to achieve this for me ?  Requinix has suggested one and while it's short I am not sure if it's safe.

 

Thanks !

[Jacques1]

Requinix has suggested one and while it's short I am not sure if it's safe.

 

Casting IDs into PHP integers is not a good idea, because it can change the user input (which is potentially dangerous) and will fail when the IDs exceed the upper limit of the PHP integer type.

 

There is no easy solution, mainly because the mysqli API is poorly designed. So before you introduce bugs or poke holes into the application security, I'd go with the long version.

[ajoo]

Thanks Guru Jacques ! Then I'll stick to the longer version too. 

[requinix]

[edit] You know what? Nevermind. Feeding the troll is the real detriment. ajoo, do your own research before believing one anonymous person with a fancy green nameplate on some site on the internet.

 

And with that, I sign off from PHP Freaks until later this month when ~stuff~ will happen, because if Jacques wants to continue spreading his FUD then I don't want to be around for it.

Edited September 11, 2016 by requinix

[Jacques1]

Are you having your period, or what?

 

I have no idea what you're reading into my post, but I'm simply pointing out two problems:

• There's no guarantee that the MySQL data type of the ID column fits into a PHP integer. For example, a 32-bit PHP integer cannot even hold a MySQL INT UNSIGNED, let alone a BIGINT. And a MySQL BIGINT UNSIGNED doesn't fit into a PHP integer on any platform.
• When the input isn't an integer due to an error, casting silently changes this data and keeps the application going. This is no big deal for queries without side effects. But if I'm trying to change a record and get the ID wrong, I definitely do not want the application to “guess” which record I meant.

[kicken]

If you consider $staff_id = NULL to mean all records and non-null to mean a single record with that ID then you can handle it by simply binding the value twice.

 

$staff_id = 34;

$query = "SELECT fname, lname, city, email, cell FROM staff WHERE ? IS NULL OR staff_id=?";
$stmt = $con->prepare($query);
$stmt->bind_param('ii',$staff_id, $staff_id);
$stmt->execute();
$stmt->bind_result($fname, $lname, $city, $email, $cell);
$stmt->store_result();
$stmt->close();

[ajoo]

Hi Kicken,

 

Thanks for the reply and another idea. 

 

In the application code, the staff_id comes from POST.  

 

The if statement is more like 

if(isset(staff_id) && $staff_id>0)  // since no staff has a 0 id or NULL for that matter

So in case the staff_id is not set is it equal to NULL? I wonder. It would be more like undefined and NULL i think. Please correct me if I am wrong but I think that would / could lead to ambiguous results if I implement your example.

 

Thanks again.

Edited September 12, 2016 by ajoo

[kicken]

Just make it null and then change it if the post data contains a value.

 

$staff_id = null;
if (isset($_POST['staff_id'])){
    $staff_id = $_POST['staff_id'];
}

[ajoo]

Hi Kicken ! 

 

I think this should do the trick. I'll try it out and revert.

 

Thanks you.