adding join query in php

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!

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. :smiley:

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.

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

Sponsor our Newsletter | Privacy Policy | Terms of Service