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

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 »

Incidentally, can it be assigned with a global variable?
The above script will output 3. By declaring $a and $b global within the function, all references to either variable will refer to the global version. There is no limit to the number of global variables that can be manipulated by a function.

A second way to access variables from the global scope is to use the special PHP-defined $GLOBALS array. The previous example can be rewritten as:

Example #2 Using $GLOBALS instead of global
<?php
$a = 1;
$b = 2;

function Sum()
{
$GLOBALS['b'] = $GLOBALS['a'] + $GLOBALS['b'];
}

Sum();
echo $b;
?>
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 »

Celauran wrote:You can use both. You can refactor piecemeal. If you're at the stage where you're refactoring the menu, then you'll also need to update any code it relies on. That's how refactoring works.

What you're getting below doesn't look like an error, it looks like the output of var_dump.
Ok let me put it another way - it's producing that where it was producing an error before, and the script is not outputting anything, and PHP is aborting at that stage in the code.
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 »

While we were debugging this I had suggested adding a var_dump follow by an exit. That would do it. Check that that code has been removed.
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 »

Stay away from global variables. Pass your methods anything they need.
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 »

Yes when I read your comment, I knew what you meant "Exit". And thankfully, scripts without PDO still run anyway.

I assume this is the correct "PDO" way to do a manual ORDER BY?

Code: Select all

$query = "SELECT id, catid, catname, uk_catname FROM products WHERE pause = 'off' AND catid IS NOT NULL GROUP BY catname ORDER BY 
catname='Shirt' DESC, catname='Trouser' DESC";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
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 »

Absolutely. MySQL syntax doesn't change regardless of which driver you're using. The only change you'll need to make within the queries themselves is how you pass in variables.
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 »

Fab.
I did noticed pages ago (or on the other thread) that there is a slightly shorter version of the $query part, which includes the $pdo->query bit. Basically one line less.

Is that a better option?
I will need to read up on Binding, as in my subcategory menu I ought to bind it for security I think.
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

foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'].' '.$row['field2']; //etc...
}

$stmt = $db->query('SELECT * FROM table');
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['field1'].' '.$row['field2']; //etc...
}

These two. They are much shorter, and I am sure can use "OBJECT", which is how all my code is done (easier to adapt the query than the rendered part.
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 »

PDO::query() does not escape parameters for you the way prepared statements do. It's fine to use when no variables are being passed to the query. Otherwise, I'd stick with prepared statements to avoid having to escape variables manually. Also, PDOStatement::bindParam() is somewhat extraneous as you can simply pass an array of variables to PDOStatement::execute()
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 »

So what we have worked on so far, is much safer and should be adhered to?
I've just worked out ... $num_rows = $rowcount->rowCount(); .... successfully.

Not entirely sure this is correct or safe tho, since it passes a digit in the URL:

Code: Select all

$s = isset($_GET['s']) ? $_GET['s'] : null;
$query = "SELECT id, subname, uk_subname, subid, catname, uk_catname, catid, title, uk_title FROM products WHERE subid = '$s' AND pause = 'off' ORDER BY title";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) {

}
Am I doing it right, or not quite perfectly?
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 »

This is a case where I'd use a prepared statement.

Code: Select all

<?php

$s = isset($_GET['s']) ? $_GET['s'] : null;
$query = "SELECT id, subname, uk_subname, subid, catname, uk_catname, catid, title, uk_title FROM products WHERE subid = :s AND pause = 'off' ORDER BY title";
$stmt = $pdo->prepare($query);
$stmt->execute(array(':s' => $s));
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {

}
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 »

Ok, so if there are say THREE areas to query:
Where subid = :S AND catid = :c AND userid = NULL...
How would you show that in the code, and execute it in that array?

Code: Select all

$stmt->execute(array(':s' => $s) ... (':c' => $c));
Bit lost with the multiples, and with the NULL values.

Also, do you need to 'prepare' if it is just for a count?

Code: Select all

$rowcount = $pdo->query("SELECT id FROM products WHERE subid = :s AND pause = 'off'");
$num_rows = $rowcount->rowCount();
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 just passing a regular named array, so

Code: Select all

$stmt->execute(array(
    ':s' => $s,
    ':c' => $c,
));
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 »

Should this work??

Code: Select all

$query = "SELECT id, catid, catname, subid, subname FROM products WHERE catid = :c AND pause = 'off' GROUP BY subname ASC";
$result = $pdo->prepare($query);
$result->execute(array(':c' => $c));
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
}
It's producing nothing, and $c is definitely in the top of the code and in the URL.

I ask, because this works just fine:

Code: Select all

$query = "SELECT id, subname, uk_subname, subid, catname, uk_catname, catid, title, uk_title FROM products WHERE subid = :s AND pause = 'off' ORDER BY title";
$result = $pdo->prepare($query);
$result->execute(array(':s' => $s));

while ($row = $result->fetch(PDO::FETCH_OBJ)) {

}
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 »

That looks like it should work. Have you checked the value of $c?

EDIT: Have you run this query manually? GROUP BY foo ASC doesn't look right. Did you mean ORDER BY?
Post Reply