12 Replies - 1202 Views - Last Post: 25 August 2014 - 09:38 AM Rate Topic: -----

#1 Now2407   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 43
  • Joined: 18-April 14

help with prepared statements in php and mysqli

Posted 22 August 2014 - 01:03 PM

hello,
im trying to code a mysql db search engine for my site using LIKE but it wont work when i use prepared statements? i tried every thing i could think of but it won't even echo connected to my database?? i'm really stumped.
could you give me any suggestions? (by the way im using oop mysqli not PDO)
any help would be appreciated.

heres the code

search.html

<!doctype html>
<html>
<head>
    <title></title>
</head>
<body>
    
    
    <form action="labookinsearch.php" id="searchform" method="POST">
        <input type="text" name="search" placeholder="search">
        <input type="text" name="city" placeholder="city">
        <input type="submit" name="submit" value="search">
        
    </form>
    
</body>
</html>


and heres labookinsearch.php
<?php
include("db.php");
//error_reporting(E_ALL);


$term = "%{$_POST['search']}%";
$city = "%{$_POST['city']}%";

//$stmt = $con->stmt_init();
if($stmt = $con->prepare("SELECT id, name FROM markers WHERE name LIKE  ? AND city LIKE ? OR address LIKE ? ")){
    if($stmt->bind_param("sss", $term, $city, $city)){
        echo "binded";
    }else{
        
        echo "faild to bind";
    }
    if($stmt->execut()){
        
        echo "executed";
    }else{
        echo "excute faild";
    }
    if($stmt->bind_result($result)){
        echo "binded result";
    }else{
        echo "did not bind result";
        
    }
    if($result = $stmt->fetch_assoc()){
        echo "fetched array";
    }
    echo "did not fetch array";
    }   
}

?>
<!doctype html>

<html lang="en">
<head>
    <title><!-- Insert your title here --></title>
</head>
<body>
<?php if($query->num_rows != 0){
    if(!empty($term)&&(!empty($city))){
    do{?>
 <a href="place.php?placeID=<?php echo $result["id"];?>"><?php echo $result["name"]?><br></a>
    <?php } while($result = $stmt->fetch_assoc());



                            }else{ echo "you must fill both feilds";
                                    }
}else{
    
    echo "no places found";
}
?>
    
</body>
</html>


also heres the other page i was coding thats connected to labookinsearch.php, place.php
(sorry i don't use prepared statements here, im still working on it)
<?php
require("db.php");
$placeID = $_GET["placeID"];
    
if ($sql = $con->query("SELECT * FROM markers WHERE id=$placeID")) {
    //printf("Select returned %d rows.\n", $sql->num_rows);
}
$result = $sql->fetch_assoc();
$address = $result["short_address"];
$price = $result["price"];
$name = $result["name"];
$link = $result["website_link"];
$city = $result["city"];
$st = $result["state"];
$zip = $result["zipcode"];
$phone = $result["phonenum"];

?>   
<html>
<head>
    <title><?php echo $name;?></title>
</head>
<body>
    
    <h1 style="margin: 0px; padding: 0px; font-size: 40px; font-weight: normal; "><?php echo $name;?></h1>
    <h1 style="margin: 0px; padding: 0px; font-size: 20px; font-weight: normal"><?php echo $address;?></h1>
    <h1 style="margin: 0px; padding: 0px; font-size: 22px; font-weight: normal"><?php echo $city . "," . " " .   $st . " " .$zip;?></h1>
    <h1 style="margin: 0px; padding: 0px; font-size: 20px; font-weight: normal"><?php echo $phone ?></h1>
    <h1 style="margin: 0px; padding: 0px; font-size: 20px; font-weight: normal;"><a href="<?php echo $link?>" style="text-decoration: none; color: gray; border:none"><?php echo $name?> website</a></h1>
</body>
</html>

This post has been edited by Now2407: 22 August 2014 - 01:04 PM


Is This A Good Question/Topic? 0
  • +

Replies To: help with prepared statements in php and mysqli

#2 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2021
  • View blog
  • Posts: 6,151
  • Joined: 15-January 14

Re: help with prepared statements in php and mysqli

Posted 22 August 2014 - 01:29 PM

What exactly happens when you run that page? Where is it supposed to print that you're connected to the database?
Was This Post Helpful? 0
  • +
  • -

#3 Now2407   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 43
  • Joined: 18-April 14

Re: help with prepared statements in php and mysqli

Posted 22 August 2014 - 01:30 PM

View PostArtificialSoldier, on 22 August 2014 - 01:29 PM, said:

What exactly happens when you run that page? Where is it supposed to print that you're connected to the database?

when i run the page its supposed to echo the connection "connected" and then search and display links.
Was This Post Helpful? 0
  • +
  • -

#4 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2021
  • View blog
  • Posts: 6,151
  • Joined: 15-January 14

Re: help with prepared statements in php and mysqli

Posted 22 August 2014 - 01:32 PM

Quote

its supposed to echo the connection "connected"

I don't see where you're telling it to do that, but if it's not doing anything at all then you probably have a syntax error, and it's probably going to log file or some place that you're not checking.

http://www.piliapp.c...p-syntax-check/
Was This Post Helpful? 0
  • +
  • -

#5 Now2407   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 43
  • Joined: 18-April 14

Re: help with prepared statements in php and mysqli

Posted 22 August 2014 - 03:03 PM

it binds it now but does not loop and display an assoc array.

it says "connectedbindedexecutedbinded result" but it doesn't echo the variables in my code?

<?php
include("db.php");
//error_reporting(E_ALL);


$term = "%{$_POST['search']}%";
$city = "%{$_POST['city']}%";

$stmt = $con->stmt_init();
if($stmt = $con->prepare("SELECT id, name FROM markers WHERE name LIKE  ? AND city LIKE ? OR address LIKE ? ")){
    if($stmt->bind_param("sss", $term, $city, $city)){
        echo "binded";
    }else{
        
        echo "faild to bind";
    }
    if($stmt->execute()){
        
        echo "executed";
    }else{
        echo "excute faild";
    }
    if($stmt->bind_result($id,$name)){
        echo "binded result";
    }else{
        echo "did not bind result";
        
    }
    $stmt->store_result(); 
    $res = $stmt->get_result(); // here you go
    $row = mysqli_fetch_assoc($res);
    

?>
<!doctype html>

<html lang="en">
<head>
    <title><!-- Insert your title here --></title>
</head>
<body>
    
    <p>results</p>
<?php if($row->num_rows != 0){
    if(!empty($term)&&(!empty($city))){
    do{?>
 <a href="place.php?placeID=<?php echo $row["id"];?>"><?php echo $row["name"]?><br></a>
    <?php } while($row = mysqli_fetch_assoc($res));

                            }else{ echo "you must fill both feilds";
                            }
}else{
    echo "no places found";}
    }
?>
    
</body>
</html>


is there any other problems as to why its not working?

This post has been edited by Dormilich: 22 August 2014 - 03:39 PM

Was This Post Helpful? 0
  • +
  • -

#6 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4192
  • View blog
  • Posts: 13,244
  • Joined: 08-June 10

Re: help with prepared statements in php and mysqli

Posted 22 August 2014 - 03:44 PM

I see that you try to get the results in different ways, which may get in its way, eventually.
Was This Post Helpful? 0
  • +
  • -

#7 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3786
  • View blog
  • Posts: 13,717
  • Joined: 08-August 08

Re: help with prepared statements in php and mysqli

Posted 22 August 2014 - 05:58 PM

Explain the indenting here:
<?php if($row->num_rows != 0){
    if(!empty($term)&&(!empty($city))){
    do{?>
 <a href="place.php?placeID=<?php echo $row["id"];?>"><?php echo $row["name"]?><br></a>
    <?php } while($row = mysqli_fetch_assoc($res));

                            }else{ echo "you must fill both feilds";
                            }
}else{
    echo "no places found";}
    }
?>


You may think I'm nit picking, but indenting is an important part of making your code readable, and readable code is critical to debugging.
Was This Post Helpful? 1
  • +
  • -

#8 Now2407   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 43
  • Joined: 18-April 14

Re: help with prepared statements in php and mysqli

Posted 22 August 2014 - 08:23 PM

here's the explanation:

// first i check to see if there are any rows to return in the search
<?php if($row->num_rows != 0){
/* then i check to see if both city and search term are filed because you can't display a map with out a city and the search term */
    if(!empty($term)&&(!empty($city))){
    do{?>
 // then I grab the search term ids from my database and there dynamic links so i can return them in there search results
 <a href="place.php?placeID=<?php echo $row["id"];?>"><?php echo $row["name"]?><br></a>
    <?php } while($row = mysqli_fetch_assoc($res));
                            // this ends the city and search filled check
                            }else{ echo "you must fill both feilds";
                            }
}else{
// this is displayed if the num_rows are 0 or no results are found
    echo "no places found";}
    }
?>

This post has been edited by andrewsw: 22 August 2014 - 09:49 PM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is offline

  • blow up my boots
  • member icon

Reputation: 6544
  • View blog
  • Posts: 26,532
  • Joined: 12-December 12

Re: help with prepared statements in php and mysqli

Posted 22 August 2014 - 09:51 PM

That explains the code, not the erratic indenting. For example, why does line 10 start half-way across the page, but line 12 is flush on the left.

This post has been edited by andrewsw: 22 August 2014 - 09:53 PM

Was This Post Helpful? 1
  • +
  • -

#10 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3786
  • View blog
  • Posts: 13,717
  • Joined: 08-August 08

Re: help with prepared statements in php and mysqli

Posted 23 August 2014 - 05:43 AM

Here's how I'd indent that code:
<?php
if($row->num_rows != 0) {
	if(!empty($term)&&(!empty($city))) {
		do {
			echo '<a href="place.php?placeID='.$row["id"].'">'.$row["name"].'<br></a>';
		} while($row = mysqli_fetch_assoc($res));
	} else { 
		echo "you must fill both feilds";
	}
} else {
	echo "no places found";
}
}
?>


Having done it, I now see that you've got an extra closing brace in line 13. Do you see how that's easy to spot with proper indenting?
Was This Post Helpful? 1
  • +
  • -

#11 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4192
  • View blog
  • Posts: 13,244
  • Joined: 08-June 10

Re: help with prepared statements in php and mysqli

Posted 23 August 2014 - 11:58 AM

one issue that springs to mind: never ever use do…while on a variable you have no control over. that means, if you database query returns no result, you would treat false (that is what every (single row) fetch function returns on an empty set returns) as an array, which PHP will rightfully complain about.
Was This Post Helpful? 0
  • +
  • -

#12 no2pencil   User is offline

  • Professor Snuggly Pants
  • member icon

Reputation: 6636
  • View blog
  • Posts: 30,904
  • Joined: 10-May 07

Re: help with prepared statements in php and mysqli

Posted 23 August 2014 - 04:07 PM

View PostCTphpnwb, on 23 August 2014 - 08:43 AM, said:

Having done it, I now see that you've got an extra closing brace in line 13. Do you see how that's easy to spot with proper indenting?

Huge difference between doing something with care, & just throwing code at an engine & seeing what works. Also known as hammering a square through a circle.
Was This Post Helpful? 0
  • +
  • -

#13 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2021
  • View blog
  • Posts: 6,151
  • Joined: 15-January 14

Re: help with prepared statements in php and mysqli

Posted 25 August 2014 - 09:38 AM

There's a problem here too:

    $row = mysqli_fetch_assoc($res);
?>
...
<?php if($row->num_rows != 0){


You set $row to be an associative array, and then later you check a property like it's an object.

This whole sequence is a little problematic:

    if($stmt->bind_param("sss", $term, $city, $city)){
        echo "binded";
    }else{
        
        echo "faild to bind";
    }
    if($stmt->execute()){
        
        echo "executed";
    }else{
        echo "excute faild";
    }
    if($stmt->bind_result($id,$name)){
        echo "binded result";
    }else{
        echo "did not bind result";
        
    }
    $stmt->store_result(); 
    $res = $stmt->get_result(); // here you go
    $row = mysqli_fetch_assoc($res);


For one, look at the logic. If the parameters failed to bind (if bind_param returned false), you're still trying to execute the query. If the query execution failed, you're still trying to bind a result. If binding the result failed, you're still trying to call store_result, get_result, and mysqli_fetch_assoc. Do you really want to do all of those things if parts are failing?

In some parts you're using object-oriented MySQLi, but then for some reason you call the procedural mysqli_fetch_assoc. You typically don't want to mix object-oriented and procedural code when you're working with something like mysqli.

I don't think you're getting the results correctly, it looks like you're just trying a combination of things to see if anything works, but the things you're trying don't work together. Look at the manual for bind_result, for example:

http://www.php.net/m...bind-result.php

bind_result is used with fetch, look at the example:

    /* bind variables to prepared statement */
    $stmt->bind_result($col1, $col2);

    /* fetch values */
    while ($stmt->fetch()) {
        printf("%s %s\n", $col1, $col2);
    }


You're not using fetch, so you probably don't want to use bind_result either. store_result is also used with fetch, I doubt you need to use store_result either. You can use get_result, which is going to return a result object, but instead of using the procedural functions you should still use it as an object. Check example 1 here:

http://www.php.net/m....get-result.php

Here's the result class that gets returned by get_result:

http://www.php.net/m...sqli-result.php

Note that it has a property for num_rows, and methods like fetch_assoc and fetch_object.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1