pdo duplicate error

I’ve searched all over for the solution, but the answers I find are for people that are making the same mistake:
[php]->fetch() or ->fetchAll()[/php]
I do not make this mistake, I use the correct format and still get the same results.
[php]
try
{
$db = db_connect(‘myDB’);
$search = strtolower($_GET[‘field’]);
//i am using GET vs POST for debug reasons

	$sql = "SELECT *
			FROM collection
			WHERE name LIKE ? ORDER BY name ASC";			
	$params = array("%$search%");
	$query = $db->prepare($sql);		
	$query->execute($params);
	
	if($query)            
	{
	   echo'<table>';
		
		while($link = $query->fetch(PDO::FETCH_OBJ)):	

// also done fetch(PDO::FETCH_ASSOC)) as well, but it still gives a double result
echo’

’;print_r($link);echo’
’;
bla bla bla…[/php]
the rest works fine…

what am I doing wrong?
obviously if i do a LIMIT 1 it works fine, but then I miss out on the rest of the restuls for a wildcard.
Which by the way, wild card seems to give a dupe for each entry. in other words they all come up twice

dbconnect.php
[php] function db_connect($database)
{
try {
$dbcfg = parse_ini_file(‘config.ini’);
$db = new PDO(MYSQL_TYPE.":host=".MYSQL_HOST.";dbname=".$database, MYSQL_USER, MYSQL_PASS);

        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ); 
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);            
        return $db;
    }catch(PDOException $ex) {
        echo "construct error: <br/>".$ex->getMessage();
    }
 
}[/php]

thank you in advance for any insite

While I am not sure this is correct - it DOES solve the issue:

[php]SELECT DISTINCT * … BLA BLA[/php]

any one else?

Have you read the manual? fetch Fetches a row .
http://php.net/manual/en/pdostatement.fetch.php

If you want more than one row use fetchall.

“PDOStatement::fetchAll — Returns an array containing all of the result set rows

You are using LIKE with no parameters so it could to match many times.

yes.
been programing with php for some time. recently switched to pdo
have many apps that work nicely with LIKE

even doing (direct)
[php]$query = $db->query(“SELECT * FROM table WHERE name LIKE ‘%search%’”);[/php]

gives double results (not many times)

an example dump result for “john doe” would be
[php]
stdClass Object
(
[name] =>John Doe
)

stdClass Object
(
[name] => John doe
)

stdClass Object
(
[name] => John doe jr
)

stdClass Object
(
[name] =>John doe jr
)
[/php]

regardless how I do the query, it produces double results w/o the “DISTINCT”

other methods:
[php]
$query = $db->prepare(“SELECT * FROM table WHERE name LIKE ?”);
$query = $db->prepare(“SELECT * FROM table WHERE name LIKE :search”);
[/php]

PSS
If you check the code - LIKE has a parameter, it is just producing a duplicate of it’s results.

pss
Links are nice - but give an example. php.net is old. How does your link address the duplicate issue? It is formatted as instructed by php.net. Any one who has done ANY search within the last decade, who writes with PHP will be aware of both php.net, phpfreaks and w3schools to name a few.

Post or pm me an SQL dump of your DB and the code needed to run it so I can test it myself.

Hi.
I sent the project to you a few days ago, but have not heard back. let me know if you got it.

no prob… the setup is easy
extract to htdocs (if using xampp or www folder for wamp)

edit ./config.ini, and change the db, pw info

install the included db

and your set…
thanks for looking

What are you classifying as a double resultset?

stdClass Object
(
[name] =>John Doe
)

stdClass Object
(
[name] => John doe
)

stdClass Object
(
[name] => John doe jr
)

stdClass Object
(
[name] =>John doe jr
)

Are all different, unless you are using SQL Server and capitalization is ignored.

I see
“John Doe” x2
“John Doe jr” x2

There is only one if each in the db.

Simple query, not doing any joins?

yup, just a simple

[php]select * from table where name like ‘%joh%’[/php]

Do a
$query->rowCount() on the query and see if they are actually duplicates or just different keys. If it is still a dup, send me the schema and I will take a look as well.

rowCount() = 2

but there is only one entry.
and I am using PDO::FETCH_OBJ, normally I use PDO::FETCH_ASSOC, but never fetch() on it’s own.

DISTINCT
rowCount() = 1

this is very unusual, because I have never had an issue like this with larger DB’s (like in the millions of entries where I am constantly using LIKE query)

yeah, schema and sample query please.

I sent the full source -
look at :
views/home.php

that’s where the query issue is at. easier if you have the full project to look at instead of pieces

;D

I have a fire to put out, but what is a sample query

LOL
same here - have two jobs, and one of my asst mgr are causing an issue

simple query

[php]SELECT * FROM thistable WHERE name LIKE ‘%joh%’[/php]

so a snipit from the script
[php]
$db = db_connect(‘klyxlinks’);
$search = strtolower($_POST[‘field’]);

	$sql = "SELECT 		*
			FROM 		collection
			WHERE 		name 
			LIKE 		? 
			ORDER BY 	name 
			ASC";			
	$params = array("%$search%");
	$query = $db->prepare($sql);		
	$query->execute($params);
	echo 'rowcount: '.$query->rowCount().'<br/>'; // DEBUG
	if(!empty($query) && $query->rowCount() > 0)            
	{
                   // process things here

[/php]
i’ll check my mail off and on throughout the day.

cheers!
tyvm for taking time to tinker with it.

What exactly are you searching for that is giving duplicates? When I run queries directly on the DB it works as expected.

If you run a straight query it works fine, if you run a like query that’s where I run into the problems that

I did run a like query.

Sponsor our Newsletter | Privacy Policy | Terms of Service