Switching prepared statements

[Noppy]

Hi,
So when i use normal mysqli queries i would be able to change the query by inserting extra parts into the query.eg

mysqli_query($link, "select * from table $something") or die();

$something would depend on the variables i wanted returned but could be

$something = 'Where county ="Herefordshire"';

I could then change my query depending on what a user was searching for as if they searched the county i’d be able to pick that up from the URL and change the query or add to it if more variables are added.

Using prepared statements seems too hard to be able to switch the statements out as not only do i have to change the query but i have to bind the parameters.

is there an easy way to do what i want or a different way of doing this with prepared statements.

thanks

[StarLion]

Prepare the query, show us what you’ve come up with, and we’ll point out where your $something goes

[Noppy]

sorry can’t seem to get this into the formatted code for some reason… But here is my ugly code so far. Its pretty complicated as users can search for a beach where beach name or county is like their search criteria or if it matches a postcode in my postcode table it returns everything within a radius (haven’t included postcode table query)

//Postcode table checked for match and will return the example lat/longs in the query below if they exist

if($total_num_of_postcodes >0) { 
    $specify = ", beaches.latitude,beaches.longitude, ( 3959 * acos( cos( radians('57.138') ) * cos( radians( beaches.latitude ) ) * cos( radians( beaches.longitude ) - radians('-2.092') ) + sin( radians('57.138') ) * sin( radians( latitude ) ) ) ) AS distance"; $specify_order="HAVING distance < '10' ORDER BY distance";
} else {
    $specify_order = "Order by beaches.cust_id";
}
      
      
//Lets do a query based on the search criteria
$query = "Select beaches.beach_name,beaches.mcs_id,beaches.url,beaches.county,beaches.description,beach_type.bay, beach_type.cove, beach_type.harbour, beach_type.estuary, 
beach_type.sand,beach_type.shingle, beach_type.pebbles, beach_type.rock, beach_type.mud, beach_type.marsh, beach_type.marina $specify
From beaches LEFT JOIN beach_type ON beaches.mcs_id = beach_type.mcs_id Where beaches.beach_name like  ? OR beaches.county like ? $specify_order $specify_limit";
      
// Setup parameter to be bound into query
//we've already set this in the above query
// Get instance of statement
$stmt = $gbgconn->stmt_init();
// Prepare Query
if ($stmt->prepare($query)) {
      
    // Bind Parameters [s for string]
    $stmt->bind_param("ss",$search_name, $search_name);
  
    // Execute statement
    $stmt->execute();

    // Store the result (to get properties) 
    $stmt->store_result();

    // Get the number of rows 
    $num_of_rows = $stmt->num_rows;
    // Bind result variables
    if($total_num_of_postcodes >0) {
        $stmt->bind_result($beach_name,$mcs_id, $beach_url, $county, $description, $btype_bay, $btype_cove, $btype_harbour,$btype_estuary,$btype_sand,$btype_shingle,$btype_pebbles,$btype_rock,$btype_mud,$btype_marsh,$btype_marina,$latitude,$longitude,$distance);
    } else {
        $stmt->bind_result($beach_name,$mcs_id, $beach_url, $county, $description, $btype_bay, $btype_cove, $btype_harbour,$btype_estuary,$btype_sand,$btype_shingle,$btype_pebbles,$btype_rock,$btype_mud,$btype_marsh,$btype_marina);
    }

[StarLion]

So, my first response is: Your final latitude doesnt have a table qualifier on it.

My second response is: Your distance calculation is using a fixed point of reference. If you’re only going to use a single static point of reference for distance measurement, calculate the value of distance and store it in the table, rather than asking the database to calculate it on the fly every time.

Note that if you do the above, then you can dump $specify in it’s entirety and just worry about $specify_order. It will also make your bottom sorting easier.

Other than that, what you have here should work, and is the ‘proper’ way to do it.

[Noppy]

cool thanks. Sorry its so messy at the moment. The latitude and longitude will be dynamic and pulled from the postcodes table i just used a static one to get it working.

You’ve answered my main question though that is whether this is the proper way to do it.

I guess anything i want to add in to the query from a user eg radius would need to be added to the bind_param so i’ll need an if/else on that too. Maybe neater to have 1 if/elses and put it all in rather than on separate parts.

thanks at least i know i am mostly heading the right direction

[StarLion]

Here’s the one-liner: Bind_param replaces VALUES.

So… Table name? Nope. MySQL function name? nope. The string that goes into a LIKE? sure. WHERE something = :param ? Absolutely.

[Noppy]

thanks will keep that in mind.

next question…

so i am doing a postcode check first and if that fails i am then going on to do a beach name or county query. I was thinking that seeing as UK postcodes always contain a number it would be quicker to do a

if(preg_match('#\d#',$_GET['search'])){

to see if the search variable has a number in it. If it does then i’ll do the full postcode query and if it doesn’t i can skip the postcode query and do the beach query.

Does that seem a good idea? to me it makes sense not to do the postcode query if i don’t have to.

[droopsnoot]

It would save some time to not contact the database unless you know it’s a valid postcode, but if you have a table containing all the valid postcodes and their lat/long location, might it be just as simple to do a single query against the supplied postcode to see if you get a row back? If you don’t, it’s not a postcode as far as your table is concerned.

[Noppy]

thanks. The hard part comes with not knowing if a person have put in part or full postcode, with or without space. It also may not be even be a postcode they are using to search which is why i figured it would be good to check for a number as beachname or county will never have numbers. So i’ve had to trim and remove whitespace, count string length and cut to a specific length depending on the total length.

My postcode table only contains the first part AB10 for example (around 2500 entries) the full postcode DB would be around 1 million entries so i would prefer to stick to the shortened version for speed.

The postcodes table currently has no primary index (i’ve copied this table from previous(current) website) so shouldn’t that be set to the ‘code’ field that holds the postcodes as that is what i will be querying most?

I think it is working how i expected it too now

[Noppy]

Starting to get there but need to know what is best way of doing the following.

Lets say a user is searching for a beach and i’ve set up the prepared statement and bound the parameters. If they now want to also search for whether it is lifeguarded or not i’d have to add in the parameter and it starts to get messy (there will be numerous other things they can add in too refine the search).

Instead if i bind all the parameters to start with and have them in the query but if the string is not set change the query to include an OR statement that overrides the condition e.g.

safety.lifeguards = ? OR 1=1

This allows me to keep the ? in the query so the param count is correct.

Is that a bad way of doing it?

thanks