Page 3 of 10

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

Posted: Tue Sep 03, 2013 10:11 am
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;
?>

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

Posted: Tue Sep 03, 2013 10:12 am
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.

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

Posted: Tue Sep 03, 2013 10:14 am
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.

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

Posted: Tue Sep 03, 2013 10:14 am
by Celauran
Stay away from global variables. Pass your methods anything they need.

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

Posted: Tue Sep 03, 2013 10:18 am
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)) {

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

Posted: Tue Sep 03, 2013 10:22 am
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.

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

Posted: Tue Sep 03, 2013 10:25 am
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.

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

Posted: Tue Sep 03, 2013 10:27 am
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.

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

Posted: Tue Sep 03, 2013 10:29 am
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()

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

Posted: Tue Sep 03, 2013 10:33 am
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?

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

Posted: Tue Sep 03, 2013 10:42 am
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)) {

}

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

Posted: Tue Sep 03, 2013 10:47 am
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();

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

Posted: Tue Sep 03, 2013 10:49 am
by Celauran
You're just passing a regular named array, so

Code: Select all

$stmt->execute(array(
    ':s' => $s,
    ':c' => $c,
));

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

Posted: Tue Sep 03, 2013 10:54 am
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)) {

}

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

Posted: Tue Sep 03, 2013 10:56 am
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?