php query help please.

jacko_162 · August 7, 2011

I have a sql query that pulls data from URL then finds this data in a table.

for example my URL is:

product_tag.php?tag=product

it then pulls a table and checks all results which contain the tag name product in the "name" column.

if i run the following:

echo $pid;

i get all the data but grouped together.

for example:

47484950 (should be 47, 48, 49,50)

i then need to run a query to show all rows that contain the numbers above.

confusing i know.

here is my full page code.

<?php
session_start();
    include('Includes/auth.php'); 
    require_once('header.php');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="cs" lang="cs">
<head>

        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        <meta http-equiv="content-style-type" content="text/css" />
    <meta http-equiv="content-script-type" content="text/javascript" />
    <title>Cpanel - Tag Product</title>
</head>

<body>
<?php
  if ($TAG) {
    $sql = "SELECT * FROM $table1 WHERE ID=$pid";
    $result = mysql_query($sql);
    $myrow = mysql_fetch_array($result);
    $ID = $myrow["id"];
$name = $myrow["name"];
    $make = $myrow["make"];
    $price = $myrow["price"];
    $catagory = $myrow["catagory"];
    $subcatagory = $myrow["subcatagory"];
    $description = $myrow["description"];
    $Opt1 = $myrow["Opt1"];
    $cotw = $myrow["cotw"];
$sold = $myrow["sold"];
$Opt2 = $myrow["Opt2"];
$Opt3 = $myrow["Opt3"];
$Opt4 = $myrow["Opt4"];
$Opt5 = $myrow["Opt5"];
$sold = $myrow["sold"];
    }
    $sql2 = mysql_query("select * from tags WHERE tag='$tag'");
    while ($data = mysql_fetch_array($sql2))
{
$pid = $data["pid"];

//echo $pid;


  }
  
  ?>
<div id="container">
  <div class="inner-container">
		<div class="box box-100 altbox">
				<div class="boxin">
					<div class="header">
		<h3>Related Products » Tag - <?php echo $tag; ?></h3>

</div><div class="content">
<table cellspacing="0">
							<thead>
								<tr>
								  <td><strong>Image</strong></td>
									<th><strong>Product Name</strong></th>
									<td class="tc"><strong>Catagory</strong></td>
									<td class="tc"><strong>Sub Catagory</strong></td>
									<td class="tc"><strong>Price (£)</strong></td>
									<td class="tc"><strong>Images</strong></td>
									<td class="tc"><strong>Comments</strong></td>
									<td></td>
<?
// Query to pull information from the "products" Database
$result = mysql_query("select * from $table1 WHERE id='$pid'");
while ($row = mysql_fetch_object($result)) {
?>
								</tr>
							</thead>
							<tbody>
								<tr class="first">
								  <th onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand"><a class="ico" href="product.php?ID=<?php echo $row->id; ?>"><img src="../images/products/<?php echo $row->id; ?>thumb.jpg" border="0" style="border: 1px solid #666666" width="35" height="35" /></a></th>
								  <!-- .first for first row of the table (only if there is thead) -->
									<th onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand"><a class="ico" href="product.php?ID=<?php echo $row->id; ?>"><? echo $row->name; ?></a></th>
								  <td class="tc" onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand"><? echo $row->catagory;  ?></td>
									<td onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand"><? echo $row->subcatagory;  ?></td>
									<td onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand">£<?echo $row->price;?></td>
									<td>        <div align="center">
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."one.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image One' rel='fancy2' href='../images/products/".$row->id."one.jpg'>1</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."two.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Two' rel='fancy2' href='../images/products/".$row->id."two.jpg'>2</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."three.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Three' rel='fancy2' href='../images/products/".$row->id."three.jpg'>3</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."four.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Four' rel='fancy2' href='../images/products/".$row->id."four.jpg'>4</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."five.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Five' rel='fancy2' href='../images/products/".$row->id."five.jpg'>5</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."six.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Six' rel='fancy2' href='../images/products/".$row->id."six.jpg'>6</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."thumb.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Thumbnail' rel='fancy2' href='../images/products/".$row->id."thumb.jpg'>T</a>";
} else {
    echo "";
}
?>
        </div></td>
									<td><div align="center">
<a id="iframe2" class="ico-comms"  href="viewcomments.php?ID=<?php echo $row->id; ?>">
									  <?
// Query to pull information from the "coments" Database
$ret = mysql_query("SELECT * FROM $table12 WHERE productid='$row->id' AND reply='yes'"); { echo mysql_num_rows($ret);  mysql_free_result($ret); } ?>
								    </a></div></td>
									<!-- a.ico-comms for comment-like backgrounds -->
									<td><a class="ico" id='tooltip' title='View This Item' href="product.php?ID=<?php echo $row->id; ?>"><img src="Includes/css/img/led-ico/blog.png" border="0" /></a> <a class="ico" id='tooltip' title='Edit This Item' href="edit.php?ID=<? echo $row->id; ?>"><img src="Includes/css/img/led-ico/pencil.png" border="0" /></a> <a class="ico" id='tooltip' title='Delete This Item' href="remove.php?ID=<? echo $row->id; ?>&db=products"><img src="Includes/css/img/led-ico/delete.png" border="0" /></a>
									  <? } mysql_free_result($result); ?></td>
								</tr>
							</tbody>
						</table>
            		  </div>
		  </div>
</div>
                
  </div>
		<!-- .inner-container -->
</div>

</div>
</body>

apologies for the messy code, im still learning.

MasterACE14 · August 7, 2011

$pid = $data["pid"];

should be:

$pid = $data["pid"].', ';

jacko_162 · August 7, 2011

thank you,

now when i eco $pid i get:

42, 40, 47, (it has the ending ',' does that matter?)

now in my loop i have the sql statement:

$result = mysql_query("select * from $table1 WHERE id='$pid'");
while ($row = mysql_fetch_object($result)) {

this is only showing 1 row WHERE ID='47'

how can i modify it to show all the above rows where ID=$pid ?

such a headache with this, should be simple php =(

jacko_162 · August 7, 2011

ok i changed it to put the PID into an array with the following code:

   $pid_array = array();
   
  $sql2 = mysql_query("select * from tags WHERE tag='$tag'");
   while ($data = mysql_fetch_array($sql2))  {
$pid_array[] = $data["pid"];
   print_r($pid_array);
    }

after i print_r $pid_array i get the following:

Array ( [0] => 42 ) Array ( [0] => 42 [1] => 40 ) Array ( [0] => 42 [1] => 40 [2] => 47 )

how can i modify my SQL

$result = mysql_query("select * from $table1 WHERE id='$pid'");

to pull only information in the array for the "WHERE" clause?

jacko_162 · August 7, 2011

ok for some reason i was getting 3x arrays so gone back with the original method.

my current code is:

<?php
session_start();
    include('Includes/auth.php'); 
    require_once('header.php');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="cs" lang="cs">
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <meta http-equiv="content-style-type" content="text/css" />
    <meta http-equiv="content-script-type" content="text/javascript" />
    <title>Cpanel - Tag Product</title>
</head>
<body>
<?php
    $sql2 = mysql_query("select * from tags WHERE tag='$tag'");
    while ($data = mysql_fetch_array($sql2))
{
$pid = $data["pid"].', ';
echo $pid;
  }
  ?>
<div id="container">
  <div class="inner-container">
		<div class="box box-100 altbox">
				<div class="boxin">
					<div class="header">
		<h3>Related Products » Tag - <?php echo $tag; ?></h3>
</div><div class="content">
<table cellspacing="0">
							<thead>
								<tr>
								  <td><strong>Image</strong></td>
									<th><strong>Product Name</strong></th>
									<td class="tc"><strong>Catagory</strong></td>
									<td class="tc"><strong>Sub Catagory</strong></td>
									<td class="tc"><strong>Price (£)</strong></td>
									<td class="tc"><strong>Images</strong></td>
									<td class="tc"><strong>Comments</strong></td>
									<td></td>
<?
// Query to pull information from the "products" Database
$result = mysql_query("select * from $table1 WHERE id='$pid'");
while ($row = mysql_fetch_object($result)) {
?>
								</tr>
							</thead>
							<tbody>
								<tr class="first">
								  <th onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand"><a class="ico" href="product.php?ID=<?php echo $row->id; ?>"><img src="../images/products/<?php echo $row->id; ?>thumb.jpg" border="0" style="border: 1px solid #666666" width="35" height="35" /></a></th>
								  <!-- .first for first row of the table (only if there is thead) -->
									<th onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand"><a class="ico" href="product.php?ID=<?php echo $row->id; ?>"><? echo $row->name; ?></a></th>
								  <td class="tc" onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand"><? echo $row->catagory;  ?></td>
									<td onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand"><? echo $row->subcatagory;  ?></td>
									<td onClick="document.location.href='product.php?ID=<?php echo $row->id; ?>';" style="cursor:pointer;cursor:hand">£<?echo $row->price;?></td>
									<td>        <div align="center">
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."one.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image One' rel='fancy2' href='../images/products/".$row->id."one.jpg'>1</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."two.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Two' rel='fancy2' href='../images/products/".$row->id."two.jpg'>2</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."three.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Three' rel='fancy2' href='../images/products/".$row->id."three.jpg'>3</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."four.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Four' rel='fancy2' href='../images/products/".$row->id."four.jpg'>4</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."five.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Five' rel='fancy2' href='../images/products/".$row->id."five.jpg'>5</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."six.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Image Six' rel='fancy2' href='../images/products/".$row->id."six.jpg'>6</a>";
} else {
    echo "";
}
?>
                                        <?php 
// Check if Image one is available
$filename = "../images/products/".$row->id."thumb.jpg";

if (file_exists($filename)) {
    echo "<a class='ico' id='fancybox' title='$row->name - Thumbnail' rel='fancy2' href='../images/products/".$row->id."thumb.jpg'>T</a>";
} else {
    echo "";
}
?>
        </div></td>
									<td><div align="center">
<a id="iframe2" class="ico-comms"  href="viewcomments.php?ID=<?php echo $row->id; ?>">
									  <?
// Query to pull information from the "coments" Database
$ret = mysql_query("SELECT * FROM $table12 WHERE productid='$row->id' AND reply='yes'"); { echo mysql_num_rows($ret);  mysql_free_result($ret); } ?>
								    </a></div></td>
									<!-- a.ico-comms for comment-like backgrounds -->
									<td><a class="ico" id='tooltip' title='View This Item' href="product.php?ID=<?php echo $row->id; ?>"><img src="Includes/css/img/led-ico/blog.png" border="0" /></a> <a class="ico" id='tooltip' title='Edit This Item' href="edit.php?ID=<? echo $row->id; ?>"><img src="Includes/css/img/led-ico/pencil.png" border="0" /></a> <a class="ico" id='tooltip' title='Delete This Item' href="remove.php?ID=<? echo $row->id; ?>&db=products"><img src="Includes/css/img/led-ico/delete.png" border="0" /></a>
									  <? } mysql_free_result($result); ?></td>
								</tr>
							</tbody>
						</table>
            		  </div>
		  </div>
</div>
                
  </div>
		<!-- .inner-container -->
</div>

</div>
</body>

when i run:

echo $pid;

i get:

42, 40, 47,

how can i use this to modify the code on line 43;

$result = mysql_query("select * from $table1 WHERE id='$pid'");

to pull information on all the above ID's instead of just the last one as its doing now.

jacko_162 · August 7, 2011

aaah while loop was closing before i called line 43, fixed it now.

=)

Sign In

php query help please.

Recommended Posts

jacko_162

Link to comment

Share on other sites

MasterACE14

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information