CODEWITHSUNDEEP
phpsqlcoldfusion
The old dog
The old dog

Reputation: 157

Queries: ColdFusion to PHP

Here is my table structure:

Table artwork
AID CID SCID    Title   Dimensions  Medium  ArtFilePath ArtDate DateAdded   ThumbFilePath   DisplayOrder    Comments    Details 

Table categories
CID CategoryName

Table subcategories
SCID    CID SubCategoryName SubHeaderText

The result will give me variables getNext and getPrev that I can use like this:

<cfif prevDO gt 0 and getPrev.recordCount gt 0><a href="gallery_detail.cfm?AID=#getPrev.AID#">back</a></cfif>
<a href="gallery.cfm?CID=#getArt.CID#&SCID=#getArt.SCID#">#getArt.SubCategoryname#</a>
<cfif getNext.recordCount gt 0><a href="gallery_detail.cfm?AID=#getNext.AID#">next</a>    </cfif>

I have three queries here that run in ColdFusion. AID is the art ID# CID is the CategoryName ID# i.e. Oils, Drawings, Computer Art, etc. SCID is SubCategoryName ID# i.e. Men, Women, Children, etc.

These queries select the chosen CID and SCID and give all the AIDs in that group for viewing. In other words, let me see all the oils in the men group.

<cfquery name="getArt" datasource="mssqlcf_PDartist1">
select * from Artwork a
join Categories b on (b.CID = a.CID)
join SubCategories c on (c.CID = b.CID and c.SCID = a.SCID)
<!---left join Categories on (Categories.CID = Artwork.CID)
left join SubCategories on (SubCategories.CID = Categories.CID)--->
where AID = #AID#
order by DisplayOrder
</cfquery>

<cfset nextDO = getArt.DisplayOrder + 1>
<cfset prevDO = getArt.DisplayOrder - 1>

<cfquery name="getNext" datasource="mssqlcf_PDartist1">
select AID from Artwork
where CID = #getArt.CID#
and SCID = #getArt.SCID#
and DisplayOrder = #nextDO#
</cfquery>

<cfquery name="getPrev" datasource="mssqlcf_PDartist1">
select AID from Artwork
where CID = #getArt.CID#
and SCID = #getArt.SCID#
and DisplayOrder = #prevDO#
</cfquery>

I have been trying to do the same thing with PHP starting with: 

<?php
$dbname = 'pdartist2';
    $table = 'artwork';
$result = mysql_query("SELECT * from artwork
a join categories b on (b.CID = a.CID)
join subcategories c on (c.CID = b.CID and c.SCID = a.SCID)
where AID = $AID
order by DisplayOrder") or die(mysql_error());  
while($row = mysql_fetch_array($result)){   
    $AID = $row['AID'];
    $ArtFilePath = $row['ArtFilePath'];
    $ThumbFilePath = $row['ThumbFilePath'];
    $Title = $row['Title'];
    $Dimensions = $row['Dimensions'];
    $Medium = $row['Medium'];       
    $Comments = $row['Comments'];
    $DisplayOrder = $row['DisplayOrder'];
    $Details = $row['Details']; 
}
mysql_free_result($getArt);
?>

<?php $nextDO = getArt.DisplayOrder +1; ?>

<!--getNext-->
<?php
$dbname = 'pdartist2';
$table = 'artwork';
$result = mysql_query("SELECT AID from artwork where CID = getArt.CID and SCID = getArt.SCID and DisplayOrder = $nextDO") or die(mysql_error());
mysql_query($query);
?>

The problem I'm having is results from the second query because I am not getting the results from the first query into $getart.

The question is how to run PHP queries with the same results as these ColdFusion queries?

Upvotes: 0

Views: 272

Answers (3)

Leigh
Leigh

Reputation: 28873

I am not getting the results from the first query into $getart.

That first line is a pretty succinct summary of the issue. One thing that is missing is what were the actual results versus what you were expecting? For example

"I am trying to retrieve the "DisplayOrder" value in the first row and increment it. But instead I am getting error message xyz... (OR) I am getting the value from row y instead."

My php is dubious at best, but as far as I can see there is no $getart variable declared in your code. You assigned the query result to a variable named $result, not $getart. 

$nextDO = getArt.DisplayOrder +1;

That is still CF syntax. I believe you need to fetch a row from your results object first. Then access a specific column within that row using array notation - like you are doing within the while loop. Check the online docs for the exact syntax.

But I didn't know that I could put another query inside the loop.

I do not see any looping within your CF code. If you are not looping in CF it should not be necessary in php either. That said, querying within a loop is a bad practice anyway. It is very inefficient and should be avoided when possible.

Upvotes: -1

steve
steve

Reputation: 1490

also, on both sides, cf and php, dont forget to protect yourself from sql injections

for example, coldfusion code:

where aid = < cfqueryparam cfsqltype="cf_sql_integer" value="#aid#">

Upvotes: 4

nageeb
nageeb

Reputation: 2042

First of all, your loop:

while($row = mysql_fetch_array($result)){   
    $AID = $row['AID'];
    $ArtFilePath = $row['ArtFilePath'];
    $ThumbFilePath = $row['ThumbFilePath'];
    $Title = $row['Title'];
    $Dimensions = $row['Dimensions'];
    $Medium = $row['Medium'];       
    $Comments = $row['Comments'];
    $DisplayOrder = $row['DisplayOrder'];
    $Details = $row['Details']; 
}

isn't doing anything. The while() structure will loop through each of the records returned by your sql query, but all you seem to be doing is overwriting the variables each time. Without knowing your table structures, I would think your second query should be nested within the while() loop. This will then run that query for each record returned from the first query.

Also, without knowing your table structure, I suspect this may also be accomplished by having another join on the first query, but that's up to you, i guess.

Upvotes: 0

Related Questions