Error accessing MySQL database with PHP object (nested queries)

Question

I want to get some data from a Sphinx server and pass it to MySQL to execute some queries. I'm new to PHP so probably I'm missing something here. I've looked for similar questions but can't find anything so maybe you can help me.

The error is in the first while. I'm pretty sure it's due to the $rown variable but don't know the reason. (I've verified that I can retrieve data from the connections so it is passing the data where the error lies - could be the sql syntax of the query but that seems fine).

Edited the code thanks to the comments below, now I get the error: Warning: mysqli_fetch_object() expects parameter 1 to be mysqli_result, boolean given in C:\Apache24\htdocs\test3.php on line 20. This is because the query failed, I still suspect it is because $rown.

$sphinxcon  = mysqli_connect...
$mysqlcon   = mysqli_connect...

$query      = "SELECT names FROM iproducts LIMIT 0,1000";
$raw_results= mysqli_query($sphinxcon, $query);

//Until here works ok, now I want to pass $raw_results to MySQL

while ($row = mysqli_fetch_object($raw_results)) {
    $rown = $row->names;
    $mquery = "SELECT text FROM claims WHERE EXISTS ($rown) LIMIT 0,1000"; 

    $mysqlresults = mysqli_query($mysqlcon, $mquery);

    while ($final = mysqli_fetch_object($mysqlresults)) //this is line 20
    {
        printf ("%s<br />", $final->text);
    }

}

Thanks :)

`EXISTS ($rown)` I can't imagine this does what you want it to — bassxzero, Jun 29 '17 at 19:56
AlexHowansky got it, thanks - @bassxzero but that syntax is ok right? I've seen it used here https://www.tonymarston.net/php-mysql/databaseobjects.html — Hoju, Jun 29 '17 at 19:59
@bassxzero yeah `(string)$row` is wrong, after adding the missing semicolon got the error `Object of class stdClass could not be converted to string` in that line — Hoju, Jun 29 '17 at 20:02
What is the relationship between `iproducts` and `claims`? Why are you trying to do a sub query in PHP instead of letting the mysql engine do it? — bassxzero, Jun 29 '17 at 20:04
@bassxzero need to use Sphinx for performance. Sphinx doesn't support JOIN types so i'm using this approach. iproducts is an index of a table where there is a column, `names`, with a list of products names. Essentially I need to do a SEMIJOIN against the column `text` in the table `claims` where each row is the description of a patent. — Hoju, Jun 29 '17 at 20:16

score 0 · Answer 1 · answered Jun 29 '17 at 20:19

0

Well $row contains an object, so would have to use it as such, maybe

 $rown = (string)$row->names;

... assuming you want the variable to contain the 'names' attribute you just SELECTed from Sphinx index.

As for the mysql EXISTS(), no idea what you really doing here, seems confused. How you structured it currently suggests that 'names' attribute in sphinx contains a complete SELECT query, that mysql could execute for the exists condition. That seems unlikely.

Guessing you meaning to more normal query something like

$mquery = "SELECT text FROM claims WHERE text LIKE '%$rown%' LIMIT 0,1000";

But that is subject to SQL injection, particully if names might contain single quotes. SO should escape it. Perhaps

$rown =  mysqli_real_escape_string($mysqlcon, $row->names);

But might be worth reading up on prepared queries.

btw, the 'Error' you getting, is because you creating an invalid query and not dealing with it. So $mysqlresults is FALSE.

$mysqlresults = mysqli_query($mysqlcon, $mquery) or die("Mysql Error: ".mysqli_error($link)."\n");

answered Jun 29 '17 at 20:19

barryhunter

20,886
3
30
43

Hey Barry, you are everywhere ;) I got the idea of the EXITS() from here http://stevestedman.com/wp-content/uploads/VennDiagram1.pdf I don't care about SQL injection (not right now at least) beacuse I'm doing everything locally (data analysis project for classroom). I am running your `$mquery` and it seems slow but it is running!! thanks! :) The thing is, this is running in Apache, so I lose most of the good stuff (speed) about Sphinx indexes... I'd really wish this could be done in Sphinx :) – Hoju Jun 29 '17 at 20:45
Now that I think about it, the approach I've used doesn't make sense, I should be querying the patent text and the product names from Sphinx and then doing the JOIN, don't you think? – Hoju Jun 29 '17 at 21:36
Well it does seem odd, have the use of systems backwards. Selecting the list of names would make sence as a MySQL query (not sphinx!). And the 'search inside the text field' should be a Sphinx Query (not Mysql) - sphinx is a full-text query engine, so would run queries MUCH quicker than mysql LIKE queries! – barryhunter Jun 30 '17 at 10:06
If this is actully related to your other question: https://stackoverflow.com/questions/44681622/compare-strings-of-text-between-two-tables-in-a-database-or-locally, the steps are: **1)** Select products from **mysql** **2)** for each one, run a **sphinx** full-text query against the claims table **3)** do something with data (eg insert result into **mysql** table!) – barryhunter Jun 30 '17 at 10:08

Error accessing MySQL database with PHP object (nested queries)

1 Answers1