adding join query in php

[system]

In my web application we query a database table based on a fieldname and compare to option but was asked to add a section to the output and that comes from another table. Here is the code as it stands now:

[php]$stmt = mysqli_prepare($link, "SELECT Barcode, DSN, FromID, ToID, Reason, Active, CONVERT_TZ(ModifiedDate, ‘+00:00’, ?) as LocalModifiedDate FROM EvidenceCOC Where " . (!$AllData ? “Active=1 AND " : “”) . "$Fieldname $CompareType ? ORDER BY Barcode, ID DESC, Active DESC, ModifiedDate DESC”);
mysqli_stmt_bind_param($stmt, ‘ss’, $Timezone, $_GET[‘Value’]);

				/* execute prepared statement */
				mysqli_stmt_execute($stmt);
				mysqli_stmt_bind_result($stmt, $outBarcode, $outDSN, $outFromID, $outToID, $outReason, $outActive, $outModified);[/php]

Barcode is in both tables and when I do a join query in phpmyadmin on the database it works fine but when I add it to the above code it doesn’t work. Thanks!

[xerxes]

I had a similar problem with a JOIN query, it worked absolutely fine on my PC, but wouldn’t at the remote host. As far as I can tell the only difference was that my local MySQL server was set up to use INNODB: http://dev.mysql.com/doc/refman/4.1/en/innodb-storage-engine.html whereas the remote site was using MyISAM http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html.

Strangely some JOINS work, but not others and I haven’t been able to find out why.

However, I managed to come up with a work around using sub queries, for example:

[php]$stmt = mysqli_prepare($link, “SELECT Barcode, DSN, FromID, ToID, Reason, Active, CONVERT_TZ(ModifiedDate, ‘+00:00’, ?) as LocalModifiedDate, (SELECT SomethingElse FROM AnotherTable WHERE AnotherTable.Barcode = EvidenceCOC.Barcode) AS SomethingElse FROM EvidenceCOC WHERE” . (!$AllData ? “Active=1 AND " : “”) . "$Fieldname $CompareType ? ORDER BY Barcode, ID DESC, Active DESC, ModifiedDate DESC”);[/php]

I was worried that this would be slower, but using this method: http://www.developerfusion.com/code/2058/determine-execution-time-in-php/ I tested the execution time of the page and discovered that the sub query method is actually slightly faster, although not by much, about 0.0001 seconds.

[randyw]

I managed to get it to working by using DISTINCT like so:

[php]$stmt = mysqli_prepare($link, "SELECT DISTINCT e.CaseNum, c.Barcode, c.DSN, c.FromID, c.ToID, c.Reason, c.Active, CONVERT_TZ(c.ModifiedDate, +00:00, ?) as LocalModifiedDate FROM EvidenceCOC c JOIN Evidence e USING (Barcode) Where " . (!$AllData ? "c.Active=1 AND " : “”) . “$Fieldname $CompareType ? ORDER BY c.Barcode, c.ID DESC, c.Active DESC, c.ModifiedDate DESC”);
mysqli_stmt_bind_param($stmt, ‘ss’, $Timezone, $_GET[‘Value’]);[/php]

Don’t ask me why this worked but it has but I was warned that when I get a lot of data in the table something else might sneak in if I’m not watching it. However, for now it works and I’m happy. Thank you for your help xerxes.

[xerxes]

I’ll have to try that out, although the data I’m retrieving should be distinct anyway.