New to PDO: basic script failing. Including ORDER BY...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: New to PDO: basic script failing. Including ORDER BY...

Post by Eric! »

Yeah, NULL+1 is probably undefined.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

That's what I thought. I have posted on here about sessions too. Hopefully that can be sorted as it's doing my head in. Glad this one was so straight forward - took a bit of thinking very much outside the box to suss it out!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

Code: Select all

  $query = "SELECT * FROM products WHERE (title LIKE :search OR description LIKE :search OR romancode LIKE :search) AND pause = 'off' ORDER BY title  LIMIT $offset, $rowsPerPage";
  $result = $pdo->prepare($query);
$result->execute(array(':search' => "%{$search}%"));
This has one issue. If I enter "black", it comes up with all items where black is in the title. Same for description.

But if I enter "BLACKXL2", a product "romancode", it comes up with nothing.
There is no space around the $search", so it's not like it cannot find it as the codes are all spaceless.

So why would this not work?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

Seems the only way around it was to do the romancode = :search, to be romancode LIKE :search.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

I am getting occasional errors, and am told it is possibly because this code is trying to make two DB connections.
Is it - and if not, how does it work? I thought this created the logins, and then did the older SQL Connection, or PDO if that is available.

Code: Select all

<?php
define('DBHOST', 'localhost');
define('DBUSER', 'root');
define('DBPASS', '');
define('DBNAME', 'website');

$sqlconn = mysql_connect(DBHOST, DBUSER, DBPASS);
if ($sqlconn) {
        mysql_select_db(DBNAME);
}

try {
    $pdo = new PDO('mysql:host=' . DBHOST . ';dbname=' . DBNAME, DBUSER, DBPASS);
} catch (Exception $e) {
// code goes here to tell me of problems.
}

?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post by Celauran »

There's no conditional separating them, so it is indeed creating two connections. This shouldn't cause any real problems, though. MySQL can easily support many concurrent connections. What error(s) are you getting?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

The "// code goes here" is very rarely, but sending me a lot of emails to say the database is down. One Saturday I had 42 emails, and the following weekend I had 80 in one go - literally "click by click" of users that generate it if the db does go down.

I'm told there is an error in our script, which is saying we are using "root" as the username. This isn't being used at all anywhere in our systems. The script above is a sample - I haven't obviously posted our users and passwords here. lol.

If root was in that script, then the site wouldn't work at all.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

What's wrong with this bit of PDO querying?

Code: Select all

   $query = "SELECT * FROM tickets WHERE userid =:userid AND (ticket =:search OR subject LIKE :search) ORDER BY id DESC LIMIT 0,1";
    $result = $pdo->prepare($query);
    $result->execute(array(':userid' => $userid, ':search' => '%{$search}%'));
Is it because I am putting search in a %% but also querying it as an =: as well as a LIKE : ??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post by Celauran »

You're going to need different placeholders for :search

Code: Select all

$query = "SELECT * FROM tickets WHERE userid = :userid AND (ticket = :ticket OR subject LIKE :search) ORDER BY id DESC LIMIT 1";
$result = $pdo->prepare($query);
$result->execute(array(':userid' => $userid, ':ticket' => $search, ':search' => '%{$search}%'));
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

What's wrong with this?

Code: Select all

<!DOCTYPE html>
<!--[if IE 8]>     <html lang="en" class="ie8"> <![endif]-->
<!--[if IE 9]>     <html lang="en" class="ie9"> <![endif]-->
<!--[if !IE]><!-->
<html class="large-screen responsejs" lang="en"><!--<![endif]--><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <meta charset="utf-8">
    
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0, user-scalable=0">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <link rel="stylesheet" media="all" href="/files/bs.css">

    <link rel="stylesheet" media="all" href="/files/site.css">
    <title>Responsive</title>
<meta name="keywords" content="">
        <meta name="og:title" content="Just BB Guns">
        </head>
<?php
$page= isset($_GET['page']) ? $_GET['page'] : null;
  function getPage(PDO $pdo)
  {
  $thispage="includes/".$_GET['page'].".inc";

  if (file_exists($thispage)) 
  {
   include $thispage;
  } 
  else 
  {
  echo "<meta http-equiv='Refresh' content='0 ;URL=/error'>";
  }
  }  
  ?>
<div class="body-wrapper">

<?php
if (isset($page))
{
getPage($pdo);
}
?>  
</div>
</body></html>
I'm being told:
[text]Notice: Undefined variable: pdo in C:\xampp\phpMyAdmin\justbbgunsresp\index.php on line 40

Catchable fatal error: Argument 1 passed to getPage() must be an instance of PDO, null given, called in C:\xampp\phpMyAdmin\justbbgunsresp\index.php on line 40 and defined in C:\xampp\phpMyAdmin\site\index.php on line 21
[/text]

But I'm damn sure it's correct and I copied and pasted it from a work site of ours.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post by Celauran »

Where is $pdo being set?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

Sorry what do you mean?
I have about several sites that now use pdo, none of $pdo = anything. Yet they work.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post by Celauran »

simonmlewis wrote:I have about several sites that now use pdo, none of $pdo = anything. Yet they work.
How are you using PDO, then? If you use things like $pdo->query etc, well, $pdo is being set somewhere. It's not in this page, hence the problem.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

No at this stage I am not using anything DB related. WE are testing new system. Does PDO only work when there is a database in use??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post by Celauran »

Yes. The PDO object represents a connection to the database and you need to specify the connection details in the constructor.
Post Reply