CI MySQL says no results, PHPMyAdmin has results

CI MySQL says no results, PHPMyAdmin has results - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: CI MySQL says no results, PHPMyAdmin has results (/showthread.php?tid=35138)

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]smilie[/eluser]
Hi all,

I have following 'problem'.
I am creating a 'dynamic' query based on the user input (multi select boxes).

Problem is following:
In one case, whereby some options have been selected by user CI says that result is empty.
However, when I print the query in question and paste it to the PHPMyAdmin - I do get results.

So, why does CI does not see these results, while very same query on PHPMyAdmin returns results?

Some code:

Code:
$do = $mydb->query($query);

$result = $do->result_array();

There are no MySQL errors (checked it). But CS simply 'thinks' that result is empty Sad

Thanks!

Cheers,
Smilie

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]tonanbarbarian[/eluser]
check that the query being run is exactly as you think
ci does some sanitising to queries in some cased
so after the code you have there do the following

Code:
echo $mydb->last_query();

compare the query that was run with your original

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]smilie[/eluser]
Oke, I have narrowed problem to this line (I think):

Code:
$query .= " AND something IS NOT NULL AND something != ''";

Whereby the idea is to select from database where field 'something' is either empty or NULL.

But, only when this line is added to the query, CI says: no results; but PHPMyAdmin finds 31 rows that match the query Sad

Cheers,
Smilie

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]smilie[/eluser]
Hi tonanbarbarian,

Code:
echo $mydb->last_query();

for some reason does not work. However, with:

Code:
$this->output->enable_profiler(TRUE);

I get the query as CI proccessess it.
If I copy that query and paste it to PHPMyAdmin I get 31 rows back. CI however still says 0 results Sad

I am stumped with this one Sad

Cheers,
Smilie

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]tonanbarbarian[/eluser]
well maybe the problem is is that you are not accessing the database the same way as usual

normally i access the database

Code:
$query = $this->db->query($query);

$result = $query->result_array();

echo $this->db->last_query();

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]smilie[/eluser]
Hi,

For as I know:

Code:
$query = $this->db->query($query);

$result = $query->result_array();

echo $this->db->last_query();

is same as what I am doing:

Code:
$mydb = $this->load->database('db',TRUE);

$do = $mydb->query($query);

$result = $do->result_array();

echo "last q: "; $mydb->last_query();

But it does not work. Not even when CI does get results back Smile

Cheers,
Smilie

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]tonanbarbarian[/eluser]
your echo statement is not correct

Code:
echo "last q:".$mydb->last_query();

what is the query you are running, it might have something that makes sense

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]smilie[/eluser]
Blast, you are completely right!

I have changed it and it shows query now.
However, still same problem Sad

CI says:

array(0) {} (which is var_dump of $result);

And when I copy & paste that query to PHPMyAdmin I do get 31 results back Sad

Help!

Cheers,
Smilie

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]n0xie[/eluser]
Are you sure you're connecting to the same database?

CI MySQL says no results, PHPMyAdmin has results - El Forum - 10-20-2010

[eluser]smilie[/eluser]
Uhm...
No Sad

My mistake, I was indeed looking at the PHPMyAdmin on other DB Sad

Damn, there goes 3 hours of my life I will never see back again Sad

Cheers,
Smilie