Re: mySQL query with variables, null return
- From: "Rik Wasmus" <luiheidsgoeroe@xxxxxxxxxxx>
- Date: Wed, 05 Mar 2008 21:32:56 +0100
On Wed, 05 Mar 2008 21:13:44 +0100, Trigger0219 <Trigger0219@xxxxxxxxx> wrote:
On Mar 5, 2:35 pm, "Rik Wasmus" <luiheidsgoe...@xxxxxxxxxxx> wrote:On Wed, 05 Mar 2008 20:23:13 +0100, Trigger0219 <Trigger0...@xxxxxxxxx>
wrote:
> I have a SQL query; well, two, the first with variables and the second
> uses these variables. The SQL is valid, and when I run it in the
> console and PHPMyAdmin I get two rows back --what I expect. When I
> call the proper mysql functions in PHP to retrieve the rows, I get
> nothing. Is this an issue with calling the two SQL statements? If so,
> why isn't the first query returning anything, et cetera.
> //something like this
> $sql = "SELECT (@l := lft), (@r := rght) FROM `file` FROM `file` WHERE
> f_id = ".$id; SELECT * FROM `file` WHERE lft > @l AND rght < @r";
> $res = @mysql_query($sql, $link);
> $row = @mysql_fetch_array( res, MYSQL_ASSOC);
> print_r ($row); //prints nothing
1) If you don't know what's wrong, why the hell would you surpress errors?
2) Echo mysql_error();
3) You SQL syntax is broken (double FROM ).
4) You can't run 2 queries at once using mysql_query(). However, your
variables will still exist, so:
mysql_query("SELECT lft, rght FROM `file` WHERE f_id = $id INTO @l, @r");
mysql_query("SELECT * FROM `file` WHERE lft > @l AND rght < @r");
Better yet:
mysql_query("SELECT y.*
FROM `file` x
JOIN `file` y
ON y.lft > x.lft AND y.rhgt < r.rhgt
WHERE x.f_id = $id");
Pretty basic Nested Set Model queries, there are tons of working examples
out there. Normally one would only need to check the (unique) lft value,
which could well be marginally quicker:
mysql_query("SELECT y.*
FROM `file` x
JOIN `file` y
ON y.lft BETWEEN x.lft AND r.rhgt
AND y.f_id != x.f_id
WHERE x.f_id = $id");
Apologise for my typo, But I wrote, 'something like' to show that it
isn't the actual SQL I'm running. I know it's a nested set, and that's
great you noticed it, but my query is much more complex for just a
join like that and to reduce the complexity of the SQL syntax I did
the variable route --readability > performance in this case. You'll
notice as well I printed the SQL out and tried it in a console and
PHPMyAdmin, where I got the correct results. Thanks anyway.
OK, readability over performance is a choice often made with good reason.. The first answer it is then (seperate the 2 queries). In many cases, developers are more expensive then cpu cycles and RAM :). I do apologise, I read over the 'something like' portion, I would have assumed that even if it wasn't the whole code, it was at least a 'minimum test case' you were actually trying.
Keep in mind that when debugging, remove the @'s and examine mysql_error(). The first one would've told you there was a syntax error ($res is not a valid resource..), the last one would've instantly ponted out that the ';' of the otherwise valid query (which you didn't post, but seemed to work in phpMyAdmin) was the point of failure.
--
Rik Wasmus
.
- Follow-Ups:
- Re: mySQL query with variables, null return
- From: Trigger0219
- Re: mySQL query with variables, null return
- References:
- mySQL query with variables, null return
- From: Trigger0219
- Re: mySQL query with variables, null return
- From: Rik Wasmus
- Re: mySQL query with variables, null return
- From: Trigger0219
- mySQL query with variables, null return
- Prev by Date: Re: Collate Text
- Next by Date: Re: Collate Text
- Previous by thread: Re: mySQL query with variables, null return
- Next by thread: Re: mySQL query with variables, null return
- Index(es):
Relevant Pages
|
Loading
