nested sql

hyster · May 10, 2010

i have 2 tables and i search table 1 col1 for the col2 data.

table 2, col1 has the data i need to produce the report.

im having trouble passing a variable from 1 query to the 2nd.

//1st query

$sql="select * from spl WHERE orsku LIKE '$srch' LIMIT 1";

$result=mysql_query($sql);

$rows=mysql_fetch_array($result);

$passon=$rows['orsku'];

//2nd query

$sql1="select * from dsgi WHERE reconsku LIKE ($passon)";

$result1=mysql_query($sql1);

while($rows=mysql_fetch_array($result1)){

}

i have tested the 2nd query with fixed piece of data and its ok. i think the problem is passing the data from query1 to query2.

thanks for any help

Muddy_Funster · May 10, 2010

OK, two things - 1. Please stop selecting * from your databases when you only want 1 field - it's just wrong. 2. your LIKE statement is missing operators, which you would have known if you had escaped your mysql_error() to screen.

Fix with this:

$sql="select orsku from spl WHERE orsku LIKE '%".$srch."%' LIMIT 1";
$result=mysql_query($sql) or die (mysql_error());
$rows=mysql_fetch_array($result);
$passon=$rows['orsku'];


//2nd query
$sql1="select * from dsgi WHERE reconsku LIKE ($passon)";
$result1=mysql_query($sql1) or die (mysql_error());

while($rows=mysql_fetch_array($result1)){

}

see how that goes.

hyster · May 10, 2010

im getting unexpected T_VARIABLE line 38. i can not see any thing wrong with it.

im new to using sql with php. this is a script ive cobbled together from other scripts.

the whole code if this makes it easier.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Modify DataBase</title>
<style type="text/css">
<!--
body {
background-color: #D6D6D6;
}
body,td,th {
color: #000;
}
-->
</style></head>

<body>
<p>
<?php

include("config.php"); 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Retrieve data from database 
$search=$_POST['search'];
$srch="%".$search."%";

// 1st query
$sql="select orsku from spl WHERE orsku LIKE '%".$srch."%' LIMIT 1";
$result=mysql_query($sql) or die (mysql_error());
$rows=mysql_fetch_array($result);
$passon=$rows['orsku'];

//2nd 
query$sql1="select * from dsgi WHERE reconsku LIKE ($passon)";
$result1=mysql_query($sql1) or die (mysql_error());


// Start looping rows in mysql database.

?>
</p>

<p> </p>
<form method="post" action="join.php"> 
<table width=233 align=center> 
<tr>
  <td width="88">Search for:</td><td width="133"><input type=text name='search' size=20 maxlength=255></td></tr> 
<td></td><td><input type=submit></td></tr> 
</table>
<p> </p>
</form>

<table width="800" height="72" border="1" align="center" cellpadding="3" cellspacing="0">


<tr>
<td width="113" align="center"><strong>Orig SKU</strong></td>
<td width="66" align="center"><strong>Recon SKU</strong></td>
<td width="90" align="center"><strong>Make</strong></td>
<td width="169" align="center"><strong>Model</strong></td>
<td width="58" align="center"><strong>Working</strong></td>
<td width="161" align="center"><strong>Actions</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result1)){
?>
<tr>
<td><? echo $rows['origsku']; ?></td>
<td><? echo $rows['reconsku']; ?></td>
<td><? echo $rows['make']; ?></td>
<td><? echo $rows['model']; ?></td>
<td><? echo $rows['working']; ?></td>
<td><? echo $rows['actions']; ?></td>

<td width="41" align="center"><a href="update.php?id=<? echo $rows['id']; ?>">update</a></td>
<td width="36" align="center"><a href="delete_ac.php?id=<? echo $rows['id']; ?>">delete</a></td>
</tr>



<?
}
?>
</table>


</body>
</html>

Muddy_Funster · May 10, 2010

try this and see what happens:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Modify DataBase</title>
<style type="text/css">
<!--
body {



background-color: #D6D6D6;
}
body,td,th {



color: #000;
}
-->
</style></head>

<body>
<p>
<?php

include("config.php"); 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Retrieve data from database 
$search=$_POST['search'];
if ($search == '' || $search == ' '){
echo "Search Variable empty or not passed corectly - variable is empty!";
die;
}
else{
// 1st query
$sql="select orsku from spl WHERE orsku LIKE '%".mysql_real_escape_string($search)."%' LIMIT 1";
$result=mysql_query($sql) or die (mysql_error());
$rows=mysql_fetch_array($result);
$passon=$rows['orsku'];

//2nd 
$sql1="select * from dsgi WHERE reconsku LIKE ($passon)";
$result1=mysql_query($sql1) or die (mysql_error());


// Start looping rows in mysql database.
}
?>
</p>

<p> </p>
<form method="post" action="join.php"> 
<table width=233 align=center> 
<tr>
  <td width="88">Search for:</td><td width="133"><input type="text" name="search" size=20 maxlength=255></td></tr> 
<td></td><td><input type=submit></td></tr> 
</table>
<p> </p>
</form>

<table width="800" height="72" border="1" align="center" cellpadding="3" cellspacing="0">


<tr>
<td width="113" align="center"><strong>Orig SKU</strong></td>
<td width="66" align="center"><strong>Recon SKU</strong></td>
<td width="90" align="center"><strong>Make</strong></td>
<td width="169" align="center"><strong>Model</strong></td>
<td width="58" align="center"><strong>Working</strong></td>
<td width="161" align="center"><strong>Actions</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result1)){
echo "<tr><td>".$rows['origsku']."</td><td>".$rows['reconsku']."</td><td>".$rows['make']."</td><td>".$rows['model']."</td><td>".$rows['working']"</td><td>".
$rows['actions'].'</td><td width="41" align="center"><a href="update.php?id='.$rows['id'].'>update</a></td><td width="36" align="center"><a href="delete_ac.php?id='.$rows['id'].'>delete</a></td></tr>';
}
?>
</table>


</body>
</html>

all I have done is basicly put a very very simple validation escape in there that checks the $_POST is assigning a real value to the $search variable, and stripped out the line assigning the % marks to $search as it's now included in the query. (changed the format of the output table code a little bit and removed query from query$sql1 on line 47.

let me know what you get back from this.

hyster · May 10, 2010

i uploaded and ran it with only the output table changed as it kept coming up with errors which i caould not fix ( missing , or ; )

i now get this error Unknown column 'orsku' in 'where clause'

thanks for taking the time to help me. its much appreciated.

Muddy_Funster · May 10, 2010

can you post your column names from the spl table?

hyster · May 10, 2010

spl-----splid, spl, orsku, cust, pallnum

dsgi -----id, origsku, reconsku, make, model, working, actions

Muddy_Funster · May 11, 2010

what do you get if you echo out $sql after it is built? cahnge the or die on line 42 to be:

or die (mysql_error()."<br><br>Query Text:<br>".$sql);

hyster · May 11, 2010

sent you a pm. hopefully that will make it easier for you to help.

Muddy_Funster · May 11, 2010

I think that fixed it. let us know.

hyster · May 11, 2010

which file did u edit? if it was join.php then i think i over wrote it b4 i read this post.

i have uploaded a file called muddy.php. i will not touch that one (assuming your willing to redo it).

thanks

Muddy_Funster · May 11, 2010

It was join2.php I think...

Muddy_Funster · May 11, 2010

have edited muddy.php and changed search.html form to use it. Errors are gone. Also join2.php seems to still work.

kenrbnsn · May 11, 2010

Please don't use the forums as a personal chat room. It does't help other people who want to help or need a solution to a similar problem.

hyster · May 11, 2010

sorry kenrbnsn. i gave muddy access to my webhost to make it easier for him to help me. ill post the finished code when its done.

i had to move to another of my hosts as the web host is checking for malicious content.

im now getting this error have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

echo sql = select orsku from spl WHERE orsku LIKE '%324234%' LIMIT 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

324234 being the search term

$sql="select orsku from spl WHERE spl1 LIKE '%".mysql_real_escape_string ($search)."%' LIMIT 1";
$result = mysql_query($sql) or die (mysql_error());
$rows=mysql_fetch_array($result);
$passon=$rows['orsku'];

echo "$sql";

//2nd 
$sql1="select * from dsgi WHERE reconsku LIKE ($passon)";
$result1 = mysql_query($sql1) or die (mysql_error());

echo "$sql1";

hyster · May 12, 2010

solved it this morning

$sql="select * from spl WHERE spl1 LIKE ('%$search%') LIMIT 1";
$result=mysql_query($sql) or die (mysql_error());
$rows=mysql_fetch_array($result);
$passon=$rows['orsku'];


//2nd 
$sql1="select * from dsgi WHERE reconsku LIKE '%$passon%'";
$result1=mysql_query($sql1) or die (mysql_error());

big thanks to Muddy_Funster

Sign In

nested sql

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information