PHP - Query Using 4 Tables And While Loop

this is the problem i have 2 tables

table named ZAMJENE with fields id_event_1, id_event_2
table named EVENTS with fields   id_event and event_date

and i need echo

$id_event_1 on date(EXTRACT FROM TABLE EVENTS FIELD event_date) can be replaced with $id_event_2 on date(EXTRACT FROM TABLE EVENTS FIELD event_date)

structure must remains because i have half site build already and i have 3rd table where i just need use JOIN

hope you understand problem

I am trying to create a little code, and need to get info from more than one table, preferable like so:

$query  = "SELECT xxx FROM xxx WHERE xxx="xxx" ..........
then do stuff here.

$query  = "SELECT yyy FROM yyy WHERE yyy="yyy" ..........

how to do this the right way?

Hello to everybody!

I'm developing a beach guid web site, and I need all beach spot have a Country, Region, SubRegion and Spot

And I want display this in a <ul> hierarchy.

I Create 4 tables

TABLE coutry
idCoutry
name

TABLE region
idRegion
idCoutry
name

TABLE subregion
idSubRegion
idRegion
name

TABLE beach
idBeach
idSubRegion
name

I would like display this values like that

 - Country
         - Region
               -SubRegion
                       -Beach
                       -Beach
                       -Beach
                       -Beach
                       -Beach
               -SubRegion
                       -Beach
                       -Beach
                       -Beach
                       -Beach
                       -Beach
 - Country
         - Region
               -SubRegion
                       -Beach
                       -Beach
                       -Beach
                       -Beach
                       -Beach
               -SubRegion
                       -Beach
                       -Beach
                       -Beach
                       -Beach
                       -Beach

etc.

I can do this with multi query but if I have 4000 Beachs the script made many query's

If some can help me! Thanks!             

Hey,

I have a query running inner joins... which currently works, but I need some more information pulled from other tables...

Code: [Select]
define('WPSC_TABLE_PRODUCT_LIST', "{$wp_table_prefix}wpsc_product_list");
define('WPSC_TABLE_PRODUCTMETA', "{$wp_table_prefix}wpsc_productmeta");
define('WPSC_TABLE_CATREF', "{$wp_table_prefix}wpsc_item_category_assoc");
define('WPSC_TABLE_CATNAME', "{$wp_table_prefix}wpsc_product_categories");


$cf="Linked Products";
$sku = get_post_meta($post->ID, $cf, true);
$array = explode(",",$sku);

$products = array_count_values($array);

foreach($products as $key => $value) {
$product = $wpdb->get_row("SELECT meta.product_id AS pid, list.name AS name, list.price AS price, retailer.category_id AS catid FROM ".WPSC_TABLE_PRODUCTMETA." AS meta INNER JOIN ".WPSC_TABLE_PRODUCT_LIST." AS list ON ( list.id = meta.product_id ) INNER JOIN ".WPSC_TABLE_SHOPNAME." AS retailer ON ( retailer.product_id = meta.product_id ) WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC");
?>
But I also need the category name - So I have to cross reference the following...

wordpdem_wpsc_product_list

id      name
433   itemone
432   itemtwo
431   itemthree

wordpdem_wpsc_item_category_assoc

id   product_id    category_id
1    433                    1
2    432                    2
3    410                    3

wordpdem_wpsc_product_categories

id  name
1   Bread
2   Fish
3   Snacks

I've now written:
Code: [Select]
SELECT
meta.product_id AS pid,
list.name AS name,
list.price AS price,
retailer.category_id AS catid,
category.name AS catname

FROM wordpdem_wpsc_productmeta AS meta
INNER JOIN wordpdem_wpsc_product_list AS list ON ( list.id = meta.product_id )
INNER JOIN wordpdem_wpsc_item_category_assoc AS retailer ON ( retailer.product_id = meta.product_id )
INNER JOIN `wordpdem_wpsc_product_categories``wordpdem_wpsc_product_list` AS category ON ( retailer.category_id = category.id )
WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC
However it fails on line 12 (the Where statement...)

Any ideas what I've done wrong?

Hi all,

I'm currently working on a webpage that dynamically shows tables of sales for products. I'm using SQL to store 2 tables, one containing product info (name, price etc.) and one containing sales for each product.

One on of my web pages I want to show the product name and sales for a single year. This would involve ripping the product name from my product table and the sales and year from my sales table. Is this possible in one query?

$Query= "select name from productTable and sales, year from salesTable where year = $desiredyear;

Obviously I know this is wrong because it isn't working but am I even on the right lines?

Thank you.

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' users.username='$username'");


How do I get it so it selects data from the table tickets where id='$existing', but I also want to get a user's (who is viewing the ID) position (rank). $username is a session. So, any thoughts on how I would do so?

My updated code:


<?php
session_start();
include("../includes/mysql.php");
include("../includes/config.php");

?>

<!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=iso-8859-1" />
<link href="../style.css" rel="stylesheet" type="text/css" />
<title><?php echo $title; ?></title>
</head>

<body>
<div id="container">
<div id="content">
<div id="left">
<div class="menu">
<?php include("../includes/navigation.php"); ?>
<div class="menufooter"></div>
</div>
<?php include("../includes/menu.php"); ?>
</div>
<div id="middle">
<?php
$existing = $_POST['existing'];
$ip = $_SERVER['REMOTE_ADDR'];
$username = $_SESSION['user'];

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' OR users.username='$username'");
$get = mysql_fetch_assoc($query);

if(!$existing)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>You have not enetered in a ticket ID. Please go back and do so.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['COUNT(id)'] < 1)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>The ticket ID you are trying to use doesnt exist. Please go back or submit another ticket.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['tickets.ip']==$ip || $get['user.position'] >= 1)
{
$status["tickets.status"]["0"] = "Waiting for support...";
$status["tickets.status"]["1"] = "Waiting for user...";
$status["tickets.status"]["2"] = "Ticket Closed...";
$status["tickets.status"]["3"] = "Ticket Opened...";

echo 
'
<div class="post">
<div class="postheader"><h1>View Ticket Status - ID '. $get["id"] .'</h1></div>
<div class="postcontent">
<p>Title: '. $get["tickets.title"] .' - Posted on: '. $get["tickets.date"] .'</p>
<p>Ticket Status: '. $status[$get["tickets.status"]] .'</p>
<p>Question: '. nl2br($get["tickets.question"]) .'</p>
</div>
<div class="postfooter"></div>
</div>
';

}
else
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>This is not your ticket. You only have permission to view your tickets. Please go back.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
?>
</div>
</div>
</div>
</body>
</html>

Hi

Could do with some help. I have 20 clients (client-1, client-2, client-3) and so on. Each client makes several calls per day.
How can I get a row count from two columns (Date, clid) and echo the count for each client?

Hi, I'm trying to write a query that retrieves the comments from one table where "touser=$username" and retrieve statuses from another table where byuser=$username, and I want to sort it all by date posted.

How would I go about doing that?

I'm used to writing normal single table queries, but retrieving from multiple tables is new to me.

Thank you for your help in advance.

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=306079.0

Basically, this part of the code is checking the "scanned" table in my mysql database to get the last scanned date for the item at hand.  It then takes that date and selects all the vendor items in the odbc table "ARVEND" that are greater than the last purchase date.  || This is where I am running into a problem.  When it loops through each vendor item it selects from the "associated" mysql table to see if the vendor item has been scanned.  If it does not find anything I am trying to get the $checkpurdate variable to add 1 to itself. |

In the second script you will see the elseif($checkpurdate>0) .... Here I am saying if there are any vendor items that have not been scanned, highlight the row in yellow.

But, this part of my code $checkpurdate = $checkpurdate + 1; does not seem to be counting correctly.  Sometimes it counts '4' for an item when there are only two vendor items available.

All of this may sound confusing since you may not know what I am trying to do.  But, the main problem I am having is the counting part is returning wrong numbers. $checkpurdate = $checkpurdate + 1;


Code: [Select]
/* $checkpurdate  */

 $query3  = "SELECT date FROM scanned WHERE `item` = '$ITEM' ORDER BY date DESC LIMIT 1";
$result3 = mysql_query($query3);
$checklastdate = "";
while($row3 = mysql_fetch_array($result3, MYSQL_ASSOC))
{$checklastdate= $row3['date'];
$checklastdate=date("m/d/y", strtotime($checklastdate));   }

//checkpurdate loops
$checkassoc="0";
$checkpurdate="0";
$sqlc="SELECT RECNO5 FROM ARVEND WHERE PURDATE1 > '$checklastdate' AND ITEM = '$ITEM'";
$rsc=odbc_exec($conn,$sqlc);
if (!$rsc)
  {exit("Error in SQL");}
 
while (odbc_fetch_row($rsc))
  {
  $checkrecno5=odbc_result($rsc,"RECNO5");
$checkassosquery  = "associated WHERE `VENDORID` = '$checkrecno5' AND ITEM = '$ITEM'";
$checkassoc = get_rows($checkassosquery);
 
  if($checkassoc>0)
  {
  // do nothing
  }
  else
  {
  $checkpurdate = $checkpurdate + 1;
  }
 
 
 }
 
Code: [Select]
  elseif($checkpurdate>0)
{  echo"<TR class=\"NV\"> <TD>";
}

So I output the Threads for my forum onto the main screen like so 

<div class ='grid-container'>
<?php


//Get the Threads and output them onto the screen in a grid container
$query = mysqli_query($conn, "SELECT * FROM Threads order by id desc")
   or die (mysqli_error($conn));

$GetPostsQuery = mysqli_query($conn, "SELECT Count(*) FROM Posts")
   or die (mysqli_error($conn));

while ($row = mysqli_fetch_array($query)) {
        $imageURL = 'upload/Thumbnails/'.rawurlencode($row["filename"]);
$PostBody = nl2br($row['ThreadBody']);
  echo
  
   "  <div class ='grid-item'>
	<div class='ThreadComment'> Comments: <br> </div> 
	<div class='ThreadNumber'> Post {$row['id']}<br> </div> 
      	<h2><a href='viewthread.php?id={$row['id']}'>  {$row['Title']} </a></h2> 
		<div class ='img-block'> 
		 <img src={$row['$imageURL']}$imageURL alt='' />		
		</div> 
		
       <p>$PostBody </p>
      </div> 

    \n";
}
?>

However I don't keep the count of comments against the threads on my Threads table. I've attached a "describe" of both my comments (named Posts) and my Threads table (name Threads)

I want to output the count of the comments against each Post in the above While loop

To obtain the amount of comments against a thread I can get this from SQL by doing

select count(IdOfThread) from Posts where id='169' ; 

But how would I access a query in SQL for the Posts table whilst it's already querying the Threads table? How would I implement this into the above while loop?

Many thanks (and my db design maybe incorrect I'm very new )

What I am trying to do is query a database using a loop conditional
and return the data but my code only repeats the first instance multiple times!

My code is below, I have tried to use comments to explain what should be happeing!

$rows = null;
$q1 = "SELECT * FROM table";
$r1 = mysql_query($q1) or die(mysql_error());

$i = 1;
while($i <= 5){

$start = (1000 * $i);
$end = ($start + 1000);

while($a1 = mysql_fetch_array($r1, MYSQL_ASSOC)){
/* query the database where 'start' is  >= 1000
 and where 'end' is <= 2000, this should loop so the next time is
 where 'start' >= 1000 * $i should be 1000 then 2000, 3000 etc...
 and where 'end' should be 2000, 3000, 4000 etc...
*/
if($a1['start'] >= $start && $a1['end'] <= $end){
$rows .= $a1['col1'].':'.$a1['col2']."\n";
}

}
echo nl2br(trim($rows.'<p>'));
$i++;
}

/* The problem is that it always shows multiple instances of the first result only 
where start = 1000 and end = 2000
*/
Any help would be much appreciated!! 

Hi guys, I'm trying to query two tables for different data and echo the results that match both tables. Here are the tables I have and the query I'm trying to run.
(Table)
qc_reports

(Fields)
id
report_date
report_lot_number    
report_po
report_supplier    
report_buyer    
report_inspectedby    
report_pulptemprange    
report_carrierconditions
report_supplierclaim    
report_carrierclaim    
report_temprecorder    
report_temprange_N    
report_temprange_M    
report_temprange_B    
report_suppliercontact
report_contactedby    
report_time    
report_comments

(Table)
qc_lots

(Fields)
id
report_id
lot_temprange    
lot_commodity    
lot_rpcs    
lot_brand    
lot_terms    
lot_cases    
lot_orgn    
lot_estnum
lot_avgnum

This is the query that I'm trying to do.

Code: [Select]
<?php
$sql = "SELECT * FROM qc_reports, qc_lots WHERE  ";
if (!empty($start_date) and !empty($end_date)) $sql .= " qc_reports.report_date BETWEEN '$start_date' and '$end_date' AND ";
if (!empty($search_fronteralot)) $sql .= " qc_reports.report_lot_number = '$search_fronteralot' AND ";
if (!empty($search_buyer)) $sql .= " qc_reports.report_buyer = '$search_buyer' AND ";
if (!empty($search_supplier)) $sql .= " qc_reports.report_supplier = '$search_supplier' AND ";
if (!empty($search_po)) $sql .= " qc_reports.report_po = '$search_po' AND ";
if (!empty($search_carrierconditions) and $search_carrierconditions != 'all') $sql .= " qc_reports.report_carrierconditions = '$search_carrierconditions' AND ";
 if (!empty($search_commodity) and $search_commodity != 'all') $sql .= " qc_lots.lot_commodity = '$search_commodity' AND ";
if (!empty($search_inspectedby)) $sql .= " qc_reports.report_inspectedby = '$search_inspectedby' AND ";

$sql = substr($sql, 0, -4);

$query = mysql_query($sql);


$numrows = mysql_num_rows($query);
?>
RESULTS - <?php echo $numrows; ?>
<hr>
<table width='500'><tr><td><b>Date</b></td><td><b>Lot Number</b></td><td><b>PO</b></td><td>&nbsp;</td></tr>
<tr>
<td>&nbsp;</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($query))
{
$id = stripslashes($row['id']);
$report_lot_number = stripslashes($row['report_lot_number']);
$report_po = stripslashes($row['report_po']);
$report_date = stripslashes($row['report_date']);

echo "<tr>
<td>" . $report_date . "</td></td><td>" . $report_lot_number . "</td><td>" . $report_po . "</td><td><a href='view_report.php?id=" . $id . "'>View</a></td>
</tr>";
}
echo '</table><br><br><br><hr><br><br>';

}

?>
All the variables are passed from a HTML form with $_POST. I need the search to work like this: If there is a value in a form field then the query gets appended with that value but when it gets to the $search_commodity it needs to search the second table (qc_lots) and check for the results. Any results that match have to be matched to the results from the first table (qc_reports) and display (echo) only qc_reports that match to both tables. The only common field is the report_id on the qc_lots table and the id on the qc_reports table.

I'm stuck and need some guidance. Can someone help please?

Hi,

i have a database with multiple records. One of those values is an image. When i query the database and i want only the images as a result it gives the result in a field. It then fills up the field horizontal and when there is no more field left it starts a new row. That is what i want.

But now i want some extra info next to the picture so i put that in a table with two colums like this:

echo '<table border="1" width="400"><td><a href="'.$row['website'].'"> <img src="'.$row['prod_img'].'">
               '.$row['prod_name'].' <a href="'.$row['website'].'">'.$row['website'].'</td></table>
            ';}}

But i still want the result to fill up the field and when the field is full i want the next results on a row below this....

Anyone?

I'm a bit of a newb to PHP and MySQL. I seem to be having an issue with something. How do I loop through an array, querying each value in the array until the query meets a certain condition.. In this case it would be that the number of rows returned from the query is less than five. Here is what I have:

$query1="SELECT UserID FROM Users where RefID='$userid'";
$result1=mysql_query($query1);
while ($row = mysql_fetch_array($result1, MYSQL_NUM) && $sql2querynum < '5')
{
echo ($row[0]);
echo "
";
$sql2 = "SELECT * FROM Users WHERE RefID=$row[0]";
$sql2result = mysql_query($sql2);
$sql2querynum = mysql_numrows($sql2result);
}
Problem is, for every value it echoes out, I get the following warning:
mysql_numrows(): supplied argument is not a valid MySQL result resource

Like I said, I'm a newbie to PHP to maybe I'm not even going about doing this the right way. Hoping someone can help to point me in the right direction.