Retrieving Data

[LWolf]

I have a basic table where I am trying to retrieve records that are filtered by a form request. Here is the code I have...

$ps = $pdo->prepare("SELECT * FROM `Products` Where `Vendor` LIKE '%?%'");
$ps->execute(array($_POST['Vendor']));;
echo "post=" . $_POST['vendor']; ////Displays correct data from form request
$count = $ps->rowCount();
echo "Count=" . $count; ////Count = 0 although I know for a fact that there is 1 record that should be in there

////Used for display of records
foreach ($ps as $row){
echo $row, PHP_EOL . "xxx<br>";
}

Where is the incorrect code? I am new to php and pdo. Thank you in advance

K

 

[jpadie]

There will be quotes doubled up inside the query because you are using placeholders.

There are two solutions.

SELECT * FROM `Products` Where `Vendor` LIKE concat('%',?,'%')

Or

SELECT * FROM `Products` Where `Vendor` LIKE ?

$ps->execute(array('%'.$var.'%'));
[/count]

You will also need to use a method to iterate over the resultset. Foreach($ps... Will not work as $ps is a statement object not a collection of results. Something like this is more usual
[Code]
While($row =$ps->fetch(fetchtype)){
 Print_r($row); //cannot use echo
}

 

[LWolf]

I changed the line to...
$ps = $pdo->prepare("SELECT * FROM `Products` Where `Vendor` LIKE concat('%',?,'%') ");

and Im still getting a result of 0. Suggestions?

Thank
K

 

[ChrisHirst]

Are there any rows that have an entry with a '?' after the first character and before the last?

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[LWolf]

No. There is just a single apostrophe in the vendor field.

 

[jpadie]

@Chris
Why do you ask that question?

@op
Rowcount is normally used for returning the number of affected rows for non idempotent transactions. Its use for returning the size of a resultset is database specific.

If you want to get (just) the size use count hi.

$ps = $pdo->prepare("SELECT count(*) as cnt FROM `Products` Where `Vendor` LIKE concat('%',?,'%') ");
$ps->execute(array($var));
$row =$ps->fetchobject();
echo "num rows: " . $row->cnt;

You could also use a normal query and fetchall and then count the resultant array. And you could also use the method that is built into mysql ( SQL_CALC_FOUND_ROWS)

 

[ChrisHirst]

Just trying to ascertain if the TS is trying to use '?' as a literal or a 'wildcard' in pattern matching.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[jpadie]

the ? is used as a placeholder in PDO prepare statements. it is substituted for the enquoted and escaped values in the array passed in the execute() method.

so at first glance '%?%' seems fine. but then when you think about what PDO will do with that you end up with

LIKE '%'some escaped string'%'

which won't make any sense at all to mysql. and if you used double quotes like so

LIKE "%'some escaped string'%"

then although it would work as a query it would only return values which had the single quotes within the field.

so you have to extract the LIKE wildcards to the argument

$statement->execute(array('%'.$somevariable .'%'));

or use a concat within the query itself. My preference is to keep the assembly within php, but I have not done any benchmarking on it.

 

[LWolf]

jpadie,

I am still getting a result of 0. However, when I get rid of the where clause AI am getting 8 results which is the total count in the table. This tells me that the problem is within the where clause. I am new to PDO & do not know how to troubleshoot this. As a classic asp programmer I could just print out the string from the sql statement. How do I troubleshoot this?

K

 

[jpadie]

In your first post you use vendor (lower case) to print out the variable. But Vendor (title case)in the query. Php variables are case sensitive. Might this be the issue?
Also rather than using concat to include the wildcards go with my preferred option of placing them in the array that is passed as the bound parameter.
There is nothing really to debug here as pdo works fine. The issue will be with your code and overwhelmingly likely with your variables. However if you dump the prepared statement object and the pdo object then that sometimes provides useful information.

echo "<Pre>" . print_r($pdo,true) . print_r($ps, true) . "</Pre>";

You can test your query by harcoding the value rather than relying on a form variable.

 

[LWolf]

OK, I found the problem. The string is showing...

[queryString] => SELECT * FROM `Products` Where `Vendor` LIKE concat('%',?,'%')

I hard coded a vendor and got a postive record count. I do not know enough about PDO to get the ? variable. What do I need to change? Here is the current code...

$ps = $pdo->prepare("SELECT * FROM `Products` Where `Vendor` LIKE concat('%',?,'%') ");
$ps->execute(array($_POST['Vendor']));;
$row =$ps->fetchobject

Thanks everyone!!
K

 

[jpadie]

the value for query string is what you would expect. it shows the 'prepared' string.

the issue will for sure be that you are not using the right variable. so let's try with this simple script.

note that you have to put your database connection information at the top of the script.

<?php
error_reporting(E_ALL);
ini_set('display_errors', true);

/*	change as needed */
$databaseHost = 'localhost';
$databaseName = '';
$databaseUser = '';
$databasePassword = '';
/* don't change anything else */
try{
		$pdo = new PDO("mysql:host=$databaseHost;dbname=$databaseName", $databaseUser, $databasePassword);
	} catch (PDOException $e) {
		echo $e->getMessage();
		die;
	}
?>
<!DOCTYPE html>
<head>
<title>Test Page</title>
</head>
<body>
<?php
if(isset($_POST) && isset($_POST['vendor']) && !empty($_POST['vendor'])):
	$sql = "SELECT * FROM Products WHERE Vendor LIKE ?";
	$ps = $pdo->prepare($sql);
	if($ps == false):
		print_r($pdo->errorInfo());
		die;
	endif;
	$result = $ps->execute(array( '%' . $_POST['vendor']) . '%' );
	if($result == false):
		print_r($ps->errorInfo());
		die;
	endif;
	while($row = $ps->fetchObject()):
		$rows[] = $row;
	endwhile;
	print_r($rows);
	echo "<hr/>";
endif;

$vendors = array();
$format = "<option value='%s'>%s<option>";
$sql = "Select DISTINCT Vendor FROM Products ORDER BY Vendor ASC";
$s = $pdo->prepare($sql);
if(!$s):
	print_r($pdo->errorInfo());
	die;
endif;
$result = $s->execute();
if(!$result):
	print_r($s->errorInfo());
	die;
endif;
while($row = $s->fetchObject()):
	$vendors[] = sprintf($format, $row->Vendor, htmlspecialchars($row->Vendor));
endwhile;
?>
<body>
<form method="post" >
	<fieldset>
		<legend>Select Vendor to Query<legend>
		<select name="vendor">
		<?php echo implode("\n\t", $vendors); ?>
		</select>&nbsp; <input type="submit" name="xSubmit" value="Go" />
	</fieldset>
</form>
</body>
</html>

 

[LWolf]

Ok, I have finally gotten back to this (I had to put my dog down). I copied the entire script to a new page called mytest.php. I installed my connection script and commented out your string as my connection was working (even after changing the values it would not let me connect). The page I have now displays the box with the "select vendor to query" and the submit button. The list population only displays 3 of the 22 records (by alpha order #1, 2 and 18, does not make sense). So I select 1 of the 3 vendors and get this message...

Warning: PDOStatement::execute() expects parameter 1 to be array, string given in /webroot/a/r/aremk001/primary/

http://www/Ultra/mytest.php

on line 32 Array ( [0] => [1] => [2] => )

This is line 32...

$result = $ps->execute(array( '%' . $_POST['vendor']) . '%' );

BTW...thank you for the assistance.
K

 

[vacunita]

The error is fairly self explanatory.

If you look at jpadie's code you'll notice that what he is doing is concatenating the array with a '%' thus turning the whole thing into a string.

$result = $ps->execute([b][COLOR=#A40000]array( '%' . $_POST['vendor'])[/color][/b] . [COLOR=#4E9A06][b]'%'[/b][/color] );

Try this instead:

$result = $ps->execute(array( [COLOR=#4E9A06]'%' . $_POST['vendor'] . '%'[/color] [b][COLOR=#A40000])[/color][/b]);

As far as your results go: What query are you using to populate the "Select Vendor" drop down?



I'm sorry about your dog. Been there myself, Its really hard to do.








----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[LWolf]

That worked. I have 1 record in the Products table and it returned.

As for the vendors I have table Vendors with field Vendors. The form field I use is vendor. The wierd thing about jpardie's code is the 3 results although that is not a big deal because I am getting the correct results on my page. I will work on integrating this code into my page over the next couple days and let you know how it goes.

Thank you.

K

 

[vacunita]

I meant the SQL query:

SELECT * FROM `Products`...? What are you using for that?

It sounds like the query is getting limited by something.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[jpadie]

The vendor list will only show unique values. That is the purpose of the distinct keyword.

 

[LWolf]

The Vendors field in table Vendors is a unique field and currently holds 23 records. No time to work on this until Sat.

jpardie, I am able to populate it with the current code so I am not worried that. It was the string that queryed the db I needed fixed. Ill let you guys know what happens Sunday...

Thanks

 

[jpadie]

it's not addressing the vendors table. only the products table.

 

[vacunita]

Which is why asked what the query looked like.

Obviously if your query is
SELECT * FROM `Products`...

It gets its data from the Products table.

If you want data from a Vendors table it would need to address that.

SELECT *FROM `Vendors` ....

The basis of what you get out of a database are the queries. You need to get those right to get the data




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech