Databases
StackOverflow2013
Postsdbo
POMySQL query returns rows in mysql but empty set in PHP
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL query returns rows in mysql but empty set in PHP
    primarykey
    data
    text
    <p>The following MySQL query runs in PHP without errors, but the resultset is empty. Directly outputting the query string to a file and running the query in the MySQL client using 'source [filename]' returns several rows of results, as expected.</p> <p>Is there something that would cause this query not to work with PHP? categorylinks.cl_to and smw_spec2.value_string are both varbinary(255). Show create table indicates engine=InnoDB and default charset=binary.</p> <p>Things I have tried without success:</p> <ul> <li><code>$sql = preg_replace("/[\n\t]+/", " ", $sql);</code></li> <li>Changing '_wpg' and 'Derp' to <code>CAST('_wpg' AS BINARY(255))</code></li> <li>Changing '_wpg' and 'Derp' to <code>BINARY '_wpg'</code></li> </ul> <p>I am using the MediaWiki DatabaseMysql class to execute the query and fetch rows, but it's a very thin abstraction, and I'm certain it's not the problem (see below).</p> <pre><code>SELECT prop.name AS prop_name, prop.count AS prop_count, prop.type AS prop_type, val.value AS val_value, val.unit AS val_unit, val.count AS val_count FROM ( SELECT s_id, name, type, COUNT(foo.name) AS count FROM ( ( SELECT cl.cl_to AS cat_name, s.smw_id AS s_id, s.smw_sortkey AS name, spec.value_string AS type FROM `smw_ids` s INNER JOIN (`categorylinks` cl, `page` p, `smw_ids` s2, `smw_atts2` a) ON (cl.cl_from = p.page_id AND p.page_title = s2.smw_title AND s2.smw_id = a.s_id AND a.p_id = s.smw_id) LEFT JOIN `smw_spec2` spec ON s.smw_id = spec.s_id ) UNION ALL ( SELECT cl.cl_to AS cat_name, s.smw_id AS s_id, s.smw_sortkey AS name, '_wpg' AS type FROM `smw_ids` s INNER JOIN (`categorylinks` cl, `page` p, `smw_ids` s2, `smw_rels2` a) ON (cl.cl_from = p.page_id AND p.page_title = s2.smw_title AND s2.smw_id = a.s_id AND a.p_id = s.smw_id) ) ) AS foo WHERE foo.cat_name = 'Derp' GROUP BY name ORDER BY count DESC LIMIT 10 ) AS prop INNER JOIN ( SELECT bar.p_id AS p_id, bar.value AS value, bar.unit AS unit, COUNT(bar.value) AS count, IF( @prev != p_id, @rownum := 1, @rownum := @rownum+1 ) AS rank, @prev := p_id FROM ( (SELECT a.p_id AS p_id, a.value_xsd AS value, a.value_unit AS unit FROM `smw_atts2` a) UNION ALL (SELECT r.p_id AS p_id, s.smw_sortkey AS value, NULL AS unit FROM `smw_rels2` r INNER JOIN `smw_ids` s ON r.o_id = s.smw_id) ) AS bar GROUP BY value, unit ORDER BY count DESC ) AS val ON prop.s_id = val.p_id WHERE val.rank &lt;= 50 ORDER BY prop_count DESC, prop_name, val_count DESC, val_value </code></pre> <p>Edit: The following test script outputs nothing. query.sql contains exactly the query above, written to file immediately preceding the mysql_query() call in MediaWiki's database class.</p> <pre><code>$db = mysql_connect('localhost', 'root', ''); mysql_select_db('mediawiki', $db); $res = mysql_query(file_get_contents("query.sql"), $db); while ($row = mysql_fetch_assoc($res)) { var_dump($row); } echo mysql_error($db); </code></pre> <p>Edit: I imported a huge database dump and afterwards, when I loaded the PHP page, there was a noticeable wait that seemed to indicate that the query was running, but still no results showed. I ended up reworking the query, and I no longer have this problem.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmwhite
    1. USmwhite
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL query returns rows in mysql but empty set in PHP
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL query returns rows in mysql but empty set in PHP
      1. This table or related slice is empty.
      1. VTUpMod
    1. COif the query is executing it's probably not the problem. Please post the PHP code as well.
      singulars
      1. POMySQL query returns rows in mysql but empty set in PHP
      1. USCfreak
    2. COAre you sure there are no errors? Did you try outputting `mysql_error()`?
      singulars
      1. POMySQL query returns rows in mysql but empty set in PHP
      1. USJonah
    3. COAre you sure it's 1. The same **exact** query? 2. The same **exact** server? The same user (for permissions)?
      singulars
      1. POMySQL query returns rows in mysql but empty set in PHP
      1. USircmaxell
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload