PHP - Querying A Second Table Based On 1st Queries Results

Hello I have a question. I'm trying to perform multiple queries based off the results from a query.  Is this possible ?

$result = mysql_query("SELECT id FROM sometable");

for each id returned

$result1 = mysql_query("SELECT * FROM someothertable WHERE id=result from $result query");

Any help would be GREAT

am total newbie to programming, apart from knowing SQL, the thing is i have been given a MYSQL database containing various information about kids diseases and a web interface written in php to create reports from the database that can be accessed via the interface. there are almost 25 different variables that need to be computed in the report, i have written sql queries to compute all these values, but i don't know anything about PHP, if i have all these queries isn't there a way for me to combine all these sql queries to be display results on a webpage and come up with this report without writing PHP code?

Thanks for your help

very sorry if this is too basic

So I'm trying to basically trying to make an "advanced search" function in PHP/mysql that will allow users to search by a number of different options like search by zipcode, username, gender, etc.

Now my question is how do I vary my mysql query so that it searches for all these things based on whatever the user inputs?

For example, the user might need to search zipcode and username but not gender, but in another case, might need to search username and gender, but not zipcode. Obviously I could just do some if/else statements, but that would be increasingly more difficult as there are more fields.

What can I do?

Ok, I need some serious help here.

I have a jQuery .load method that retrieves a single users name from a database. However... I also need to incorporate error checking into the script that the .load method access, so that, if the user enters a name in the field that is not in the database, it will insert it into the database, and then then pull it back out again to be returned by the ajax call.

This is the jQuery that performs the call:
Code: [Select]
$('#submit').click(function(e) {
var user = $('#user').val();
var checkurl = "indexData.php";
$('#username').load(checkurl, 'user=' + user);
e.preventDefault();
});

This is the script form indexData.php:
Code: [Select]
<?php
  $db = mysql_pconnect('localhost', 'joeme0', 'Galatians2');
if (!$db) 
{
   die('Could not connect: ' . mysql_error());
}
mysql_select_db('joeme0_brave');
$name = $_POST['name'];
$checkQuery = "select name from users where name='$name'";
$checkResult = mysql_query($checkQuery);
if ($checkResult > 1)
{
$insQuery = "insert into users (id, name) values ("", '$name')";
$insResult = mysql_query($insQuery);
if ($insResul  0)
{

$query="select name from users where name='$name'";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)) 
{ 
echo $row['name'];
}
}
else
{
echo "Cold not insert your name into the database";
}
}
else
{
$query="select name from users where name='$name'";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)) 
{ 
echo $row['name'];
}
}
?>


So far I can get it to pull out a name that is manually entered into the database no problem. But I can't get it to insert a name that isn't in the database, and then query the database a second time to pull that same name back out again.

The page is pretty simple. The user comes to the page, enters their name in the text field, either clicks the submit button or presses the enter key. The scripts fire off the ajax call to the database, check to see if the name exists, if not enter it and pull it back out (this is where I have the problem), if so, pull it out and display it back on the page.

Hi im new to php and I need help making webpage that queries a mysql database based on a 3 check boxes and displays results on the same page or on another page. The table being queried has 4 columns, name, gps, wifi, bluetooth. So for example a row in the table would be like, samsung galaxy s2, yes, yes, yes. The idea is for it to be a website that will display phones according to their features.

So the idea is depending on if the boxes were ticked the samsung galaxy would be displayed as a result. So i need some help understanding how to make this.

Some1 gave me the code below in attempt to help me (im not sure it works or not) but im not sure how fully use it, ie what pages i need to make and how i create the connection to the mysql database, and how to use the query that they wrote to display the results

thanks

code:
Code: [Select]
<form action="?do=filter" method="post">
<table cellspacing="0" cellpadding="3" border="1">
<tr>
<td>GPS<input type="checkbox" name="gps" value="checked"></td>
<td>Wifi<input type="checkbox" name="wifi" value="checked"></td>
<td>Bluetooth<input type="checkbox" name="bluetooth" value="checked"></td>
</tr>
<tr><td><input type='submit' name='filter' value='Filter'></td></tr>
</table>
</form>
</html>
<?php
function filterMe($filter){
if(isset($_POST[$filter])){
return "Yes";
}else{
return "No";
}
}

if(isset($_POST['filter'])){
echo "Gps - " . filterMe('gps');
echo "
Wifi - " . filterMe('wifi');
echo "
Bluetooth - " . filterMe('bluetooth');
}
?>


All you need to do is use a query something like
SELECT name,gps,wifi,bluetooth FROM `product` WHERE `gps`='".filterMe('gps')."' AND `wifi`='".filterMe('wifi')."' AND `bluetooth`='".filterMe('bluetooth')."'

Hi Guys. Hopefully someone can help me with this...New to coding and pretty lost on this.
I have a Mysql database which is displaying results to my webpage with no problems. However I would like to be able to add a combo box to my webpage that would update the mysql database results based on the combo box selection. For example if Ford is chosen from the combo box, the webpage would refresh and show all the results for Ford in the webpage. Can someone please help me?

Here is the code I have at the moment that works just fine. But results of the database are based on the WHERE statement.

Quote

<?php
$con = mysql_connect("server","database","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("a5525005_cars", $con);

$result = mysql_query("SELECT * FROM `cars` WHERE Makel='Ford'");

echo "<table class='ex1' border='0' width='113%' style=text-align:center; cellpadding='6' cellspacing='0'>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr style=font-family:verdana;font-size:80%;>";
 
    echo "<td width=13%>" . $row[""] . "<img src=\"" . $row["Photo"] . "\"></a>";
    echo '<td width="14%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Model'] . '</a></td>';   
    echo '<td width="5%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Year'] . '</a></td>';
    echo '<td width="4%"><a class="mylink" href="' . $row['URL'] . '">' . $row['Fuel'] . '</a></td>';
       
echo "</tr>";
  }
echo "</table>";
mysql_close($con);
?>

Hello,

I am trying to get a page that shows a list of parts, with their quantities, condition and type.

So parts that are identical are counted as 2, 3 etc if they are the same part, condition and type.

But if one or more parts of the same type has any one of those variables different to the rest, it/they need to come out on a new line with the correct quantity against them.

I have tried using arrays and I can get the quantities working against each part but don't know how to adapt it to achieve what I want.

Here is what I have so far:

// GET ALL THE PARTS FROM THIS **ONE SUPPLIER** AND FIND OUT THE QUANTITIES...

include("../dbconnectlocal.php") ;
$result = mysql_query("SELECT supplier, partNumber, newUsed, jobType, directIndirect, currency, vat, total FROM pourbaskets

  WHERE enquiryRef = '$enqId' AND supplier = '$sSup' ORDER BY

  partNumber DESC")or die(mysql_error()) ;
$quantity = 0 ;
$currentCount = 1 ;

$partQuantityArray = array() ; // The box for our parts against their quantities.
$partsBag = array() ; // For finding out how many unique parts there are later.
$partCount = mysql_num_rows($result) ; // How many parts in total.
while($row = mysql_fetch_assoc($result)) { // While we are going through each part found...

$part = $row['partNumber'] ;

$cond = $row['newUsed'] ;

$jtyp = $row['jobType'] ;

$curr = $row['currency'] ;

$vat  = $row['vat'] ;

$tota = $row['total'] ;

array_push($partsBag, $part) ;

if($partCount == 1) { // Just one part...

$quantity = 1 ;

array_push($partQuantityArray, $part, $quantity) ;

}

if(!$prevItem && $partCount != 1 { // Is the first item and not the only one...

$prevItem = $part ; // Set it to be the previous...

$quantity++ ; // Increase quantity (to 1 now)...

}

elseif($currentCount == $partCount && $partCount != 1) { // If it's the last one in the list and not the only one...

if($prevItem != $part) {

array_push($partQuantityArray, $prevItem, $quantity) ;

$quantity = 1 ;

array_push($partQuantityArray, $part, $quantity) ;

//$prevItem = $part ;

}

elseif($prevItem == $part) { // The final part is the same as the last one...

$quantity++ ;

array_push($partQuantityArray, $part, $quantity) ;

}

else {

array_push($partQuantityArray, $part, $quantity) ;

}

}

elseif($prevItem == $part) { // If the current item is the same as the last item...

$quantity++ ;

// Increase quantity...

}

elseif($partCount != 1) { // If it's a new item and not the only one, store the quantity of the last item, and start at 1...

array_push($partQuantityArray, $prevItem, $quantity) ;

$quantity = 1 ;

$prevItem = $part ;

}

$currentCount++ ;
}
print_r($partQuantityArray) ;exit();

//////////////////// END OF QUANTITY FINDER //////////////////////////////

I have attached what I would like it to come out as.

I'm trying to pull results from a database based on where the user is located based upon the variables $usr_lat & $usr_lng, and search for by a radius of x amount of miles/km (need to make it optional). I can't seem to find exactly what I'm looking for on google so I thought I'd asked here. Any help would be appreciated.

I'm very new to this and really could use some help. I've got a Web app that has one form that collects data from the user and puts it into a mysql database and has another form that allows the user to select critiera to find records in the database and display them on the page.  All this is working just fine, but now that my database is getting more data in it, I want to add functionality to display 10 records on a page with results page navigation links so the user can move forward and backward in the results set.  This part is not working and I've put in echo statements to figure out what the code is doing.

The problem I'm having is that when the selection critiera pulls more than 10 records from the database, the first page of results is correct per the selection criteria entered by the user on the select form.  When the 'next' link is selected to review the second page of results, the query is executed again.  But this time the form variables have been reset and the results now contains the entire contents of the db.  The start record is set to look at the 11th instance of the results set, so the second page starts with the 11th record in the database instead of the 11th record in the original results set.

The original select statement is built by determining which criteria is selected using $_POST against each form variable.  How can I retain the form variables or the original select statement  so the second execution of the select statement results in the same results set as the first? The other option that may be better is to retain the original results set and avoid re-executing the select statement altogether.  But I don't know how to do that either.  Any suggestions, code samples or adivice is much appreciated!

I have a search form that has a drop down
Code: [Select]
<select name="radius" id="radius">
      <option value="5">5 mi.</option>
      <option value="10">10 mi.</option>
      <option value="15">15 mi.</option>
      <option value="20">20 mi.</option>
      <option value="50">50 mi.</option>
      <option value="100">100 mi.</option>
    </select>

I'm trying to limit the results in a foreach loop within what was selected.

Meaning, if someone selects 10, results within 10 miles will show
The foreach is
Code: [Select]
foreach ($stores as $k=>$v) {
            $output = "<h3 style='margin:0;padding:0'><b>".$storeinfo[$k]['MktName']."</b><br>(approx ".$v." miles)</h3>";
            $output .= "<p style='margin:0 0 10px 0;padding:0'>".$storeinfo[$k]['LocAddSt']."<br>";
            $output .= $storeinfo[$k]['LocAddCity'].", ".$storeinfo[$k]['LocAddState']." ".$storeinfo[$k]['zipcode']."</p>";
            print_r($output);           
        }

$v being the distance. So I need to show only the results of $v that are less than $r.

How would I go about doing this?

Right now, $v displays numbers like 5.04, 173.9 and so forth.

Can anybody help me out?

Thanks in advance.

Hi All ,   I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:   Day_ID -- Dues ---  Last_Visit ---  Points. 1  --------- 900 -------- 1/12  --------   6      2  --------- 700 -------- 4/12  --------   7 3  --------- 600 -------- 7/12  --------   5 4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6.    So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:   4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   This problem stated above had been completely resolved, thanks to a lot of help from Guru Barand by this following query :-

$query = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points
FROM clubvisit cv
WHERE last_visit >=
(
SELECT MAX(last_visit) FROM clubvisit
WHERE points <>
(
SELECT points as lastpoints
FROM clubvisit
JOIN
(
SELECT MAX(last_visit) as last_visit
FROM clubvisit
) as latest USING (last_visit)
)
)";

I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.points1, cv.points2, cv.points3 etc. So now I need to make a selection based on each of these cv.points columns.   As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in just one go. This not only makes the code repetitive but also since the queries are interconnected, involves the use of transactions which I wish to avoid if possible.    The values that I require for each of the cv.point columns is  1. day_id of the previous / old day on which the cv.points value changed from the current day value, and  2. cv.points on that old/ previous day.    So for  example if the table is as below:   Day_ID -- Dues ---  Last_Visit ---  Points1 --- Points2. 1  --------- 900 -------- 1/12  -----------   9  ------------ 5    2  --------- 600 -------- 4/12  -----------   6  ------------ 6 3  --------- 400 -------- 7/12  -----------   4  ------------ 7 4  --------- 500 -------- 9/12  -----------   5  ------------ 8 5  --------- 600 -------- 10/12 ----------   6  ------------ 8 6  --------- 600 -------- 11/12 ----------   6  ------------ 8 7  --------- 600 -------- 13/12 ----------   6  ------------ 7 8  --------- 500 -------- 15/12 ----------   5  ------------ 7 9  --------- 500 -------- 19/12 ----------   5  ------------ 7   Then I need the following set of values : 1. day_id1 -- Day 7,      points1 ---- 6,          days_diff1 -- (9-7 = 2)  . // Difference between the latest day and day_id1 2. day_id2 -- Day 6,      points2 ---- 8,          days_diff2  -- (9-6 = 3) 3. day_id3 -- ....   and so on for other points.    Thanks all !

This is a little confusing so please bear with me.  I was thinking of using left.join but couldn't figure out how to implement it properly.

I am trying to order all of the results from a table names 'clans' based on how many points the clan has. To calculate the points you have to go into another table 'clanteams' and then loop every team in the clan pulling wins and losses from a row in a specific ladder table.

Here is the code i have to calculate the points.

Code: [Select]
$total[wins] = 0;
$total[loss] = 0;
$members=mysql_query("SELECT id,clanid,teamid,DATE_FORMAT(datejoined,'%M %d, %Y') FROM clanteams WHERE clanid='$member[id]'");
while(list($id,$clanid,$teamid,$joined)=mysql_fetch_row($members)){
$team=mysql_query("SELECT name,ladderid FROM teams WHERE id='$teamid'");
if(mysql_num_rows($team) == 0) continue;
$team=mysql_fetch_array($team);
$ladder=mysql_query("SELECT name FROM ladders WHERE id='$team[ladderid]'");
$ladder=mysql_fetch_array($ladder);
$linfo=mysql_query("SELECT rank,wins,loss FROM ladder_$team[ladderid] WHERE teamid='$teamid'");
$linfo=mysql_fetch_array($linfo);

$total[wins] = $total[wins] + $linfo[wins];
$total[loss] = $total[loss] + $linfo[loss];

}
$totalpoints = ($total[wins] * 2) - $total[loss];

So now i want to loop through every row in the clans table, and using the above code oder them by $total points.
Ive spent hours wrapping my head around it and still cannot figure it out.

Please help.

when I do a "SELECT * FROM system_disks WHERE system_id = 'aNumber'" and their is more than one result with that system_id, I get more than one result obviously.

I essentially need two results in one row. I've took a screen shot of what it is doing and what I need it to do.

The blurred row is a different system_id

This is what the while ($row = mysql_fetch_array($query)) is currently doing:



This is what I would like it to do:


Sorry if this is a dumb question, I've been coding the past 48 hours and my brain is fried 

Hi guys,

I am working with an old script at the moment, there is one page which just will not populate the table results.
I have tried running multiple debugging commands but the only one it flags is the line displaying Quote

last;

saying it's not a used function.
If I comment out this line, no errors are produced but the results do not enter the table.
Can anyone shed some light on this please, I've spent hours and hours and banging my head against a brick wall would probably be more constructive right now.

Many thanks indeed for any help or advice.

<?php

mysql_connect("localhost", "$db_user","$db_upwd") or die ("Error - Could not connect: " . mysql_error()); 
mysql_select_db("$database");

$query="select host,count(*) from badc_mis_prog group by host";
$result = mysql_query($query) or die ("Error - Query: $query" . mysql_error());

$count=0;
$hosts=array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $html_hlname=$row[0];
    $html_hlname=preg_replace("/</","&lt;",$html_hlname);
    $html_hlname=preg_replace("/>/","&gt;",$html_hlname);
            array_push($hosts, $html_hlname,$row[1],0);
    $count++;
        }

$query="select host,count(*) from badc_mis_prog where reported=1 group by host";
$result = mysql_query($query) or die ("Error - Query: $query" . mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $html_hlname=$row[0];
    $html_hlname=preg_replace("/</","&lt;",$html_hlname);
    $html_hlname=preg_replace("/>/","&gt;",$html_hlname);
            for ($i=0; $i<($count*3); $i+=3) {
        if ($hosts[$i] == $html_hlname) {
    $hosts[($i+2)]=$row[1];
   last;
}
    }
        }

for ($i=0 ; $i<(($count-1)*3); $i+=3){
    for ($j=$i+3 ; $j<($count*3); $j+=3){
        if ($hosts[($i+1)] < $hosts[($j+1)]){
            $temp=array();
    $temp[0]=$hosts[$i];
    $temp[1]=$hosts[($i+1)];
    $temp[2]=$hosts[($i+2)];
    $hosts[$i]=$hosts[$j];
    $hosts[($i+1)]=$hosts[($j+1)];
    $hosts[($i+2)]=$hosts[($j+2)];
    $hosts[$j]=$temp[0];
    $hosts[($j+1)]=$temp[1];
    $hosts[($j+2)]=$temp[2];
                }
    }
}
print "<br><br><br><center><table border=\"1\">\n";
print "<tr><td>Host Name</td><td>Hosted</td><td>Reported</td><td>Ratio H/R</td></tr>\n";

for ($i=0; $i<($count*3); $i+=3) {
   if ($hosts[($i+1)]<15){ break;}
           printf ("<tr><td>&nbsp; %s &nbsp;</td><td> %d </td><td> %d </td><td>%.1f %%</td></tr>\n",$hosts[$i],$hosts[($i+1)],$hosts[($i+2)],(($hosts[($i+2)]/$hosts[($i+1)])*100));
}

print "</table></center>\n";


?>

Ive gotten some results user selcts check box on first page
The php page will say which brackets it falls between example price is between 100-300 say

I dont seem to be able to populate a table with the data in the database:S

Code: [Select]
$row_number= 0;

while ($row = mysql_fetch_array($result3))
    {
If  (($row["price"] = $price_low) && ($row["price"] <= $price_high)) //price
If (($row["storage"] = $storage_low) && ($row["storage"] <= $storage_high)) //storage
if (($row["Processor "] = $processor_low) && ($row["Processor"] <= $processor_high))  //Processor
{ $row_number++;

?>

<tr>

<td align="center"><?php print $row["Computer_Price"]; ?> </td>
<td align="center"><?php print $row["Computer_Storage"]; ?> </td>
<td align="center"><?php print $row["Computer_ProcessorSpeed"]; ?> </td>

</tr>
</table>
Thats wat im usign atm the price_low and price_high are what sets the low and high price for the search