Jump to content

Similar queries


ajoo
Go to solution Solved by kicken,

Recommended Posts

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 !

 

Link to comment
Share on other sites

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?
  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 !

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

[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 by requinix
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

  • Solution

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();

  • Like 1
Link to comment
Share on other sites

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 by ajoo
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.