MySQL query returns null from PHP code but works fine in terminal

Question

When I run this code in PHP, the result is null, but when I run it in mysql terminal or phpmyadmin, I get what I want.

PHP

if ($_GET["action"] == "list") {
    //Get records from database
    $mainQuery = mysql_query("
    SET SQL_BIG_SELECTS=1;
    SELECT
      ci.id AS item_id,
      ar.title, ar.introtext, 
      flo.value AS logo, fph.value AS phone, fad.value AS address, fur.value AS url, fse.value AS services, fma.value AS map,
      ar.id AS joomla_id, ci.hidden_id, ci.type
    FROM kd9fb_content ar
      RIGHT JOIN calc_settings cs ON ar.catid = cs.joomla_cat  
      LEFT JOIN kd9fb_fieldsattach_values flo ON flo.articleid = ar.id AND flo.fieldsid = 1
      LEFT JOIN kd9fb_fieldsattach_values fph ON fph.articleid = ar.id AND fph.fieldsid = 2
      LEFT JOIN kd9fb_fieldsattach_values fad ON fad.articleid = ar.id AND fad.fieldsid = 3
      LEFT JOIN kd9fb_fieldsattach_values fur ON fur.articleid = ar.id AND fur.fieldsid = 4
      LEFT JOIN kd9fb_fieldsattach_values fse ON fse.articleid = ar.id AND fse.fieldsid = 5
      LEFT JOIN kd9fb_fieldsattach_values fma ON fma.articleid = ar.id AND fma.fieldsid = 6
      LEFT JOIN calc_item ci ON ci.joomla_id = ar.id
      ORDER BY ci.id DESC;
    ", $con);

    while ($row = mysql_fetch_array($mainQuery)) {      
        $rows[] = $row;
    }
    //Return result to jTable
    $jTableResult = array();
    $jTableResult['Result'] = "OK";
    $jTableResult['Records'] = $rows;
    print json_encode($jTableResult);
}

This returns NULL:

$row = mysql_fetch_array($mainQuery)

MySQL base is ok, the code connects to base by this:

$con = mysql_connect($host, $user, $password) or die("DB login failed!");
mysql_select_db($db, $con) or die("select failed");
mysql_query("SET NAMES utf8");

A similar code, but with other query work fine, I tested it, apparently the case in the request. By the way, I think that it is built entirely non-optimal way, but I'm not good at SQL and PHP.

So what's the problem and where I went wrong?

@bruno.karklis The result should be like this, but much more difficult:{"Result":"OK","Records":[{"0":"1","id":"1","1":"asd","name":"asd"}]} — Shadow_
– Shadow_, Commented Sep 25, 2013 at 3:43
@bruno.karklis I've tried fetch_row, but result is still :{"Result":"OK","Records":null}. I think the problem is in mysql query, but I don't know where. — Shadow_
– Shadow_, Commented Sep 25, 2013 at 3:46
@JayHarris What is the fundamental difference between my code and if I used PDO? Of course, the fact that I gave no final code, and I will need to handle it well, make safer from sql injection. But now the code does not run even in such simple form. — Shadow_
– Shadow_, Commented Sep 25, 2013 at 3:50

francisco.preller · Accepted Answer · 2013-09-25 03:41:25Z

4

Your query actually has two queries in it.

"SET SQL_BIG_SELECTS=1;
SELECT
  ci.id AS item_id,
  ar.title, ar.introtext, ....."

Remove the first line of the query and it should be okay. This is because the mysql_query() method only supports one query. But I think this restriction is removed in the terminal IIRC.

If you need the first statement simply perform two query methods:

mysql_query("SET SQL_BIG_SELECTS=1");
$query = "..."; // Rest of your query here

answered Sep 25, 2013 at 3:41

francisco.preller

6,6494 gold badges30 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query returns null from PHP code but works fine in terminal

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related