PHP - Results In A 3 Column Table

Hey all, first post to this site. I'm somewhat new to PHP, so bear with me - this might be an easy question, it might not be. Just looking for a little help.

Basically, I have a query which takes commands from a form through AJAX (day, time, time1). The query executes a wildcard on a table named sip_data, searches for linked $id (so if $id=3, for example in multiple tables, it spits out the $name, $zip in a div).

Here's the code:

Code: [Select]
<?php
$dbhost = "localhost";
$dbuser = "";
$dbpass = "";
$dbname = "";

//Connect to MySQL Server
$link = mysql_connect($dbhost, $dbuser, $dbpass);

//Select Database
mysql_select_db($dbname) or die(mysql_error());

// Retrieve data from Query String
$id = $_GET['id'];
$name = $_GET['name'];
$zip = $_GET['zip'];
$server_url = $_GET['server_url'];
$day = $_GET['day'];
$time = $_GET['time'];
$time1 = $_GET['time1'];

// Escape User Input to help prevent SQL Injection
$id = mysql_real_escape_string($id);
$name = mysql_real_escape_string($name);
$zip = mysql_real_escape_string($zip);
$server_url = mysql_real_escape_string($server_url);
$day = mysql_real_escape_string($day);
$time = mysql_real_escape_string($time);
$time1 = mysql_real_escape_string($time1);
//build query

$query = "SELECT * FROM $day,sip_data WHERE $day.id=sip_data.id AND $day$time<=>$day$time1 ORDER BY zip ASC";

//Execute query
$qry_result = mysql_query($query) or die(('No Results'));

//Build Result String
while($row = mysql_fetch_array($qry_result))

{
echo "<table id=query_result align=left>";
echo "<tr>";
    if($zip = $row[zip]); 
echo "<th><b>$row[zip]</b></th>";
echo "</tr>";
echo "<tr>";
echo "<td><a href=$row[server_url] rel=ajaxDiv>$row[name]</a></td>";
echo "</tr>";
echo "</table>";
}
echo $display_string;




?>





Basically, my issue with this is that some of the results within the sip_data DB will have zip codes that repeat. The current code prints out each results with the zip code and name - here's an image of a sample result:



What I would like to do is avoid repeating the zip code and just group the results under each zip code, kinda like this:

55408           55412        55423
---------     ------------    ----------
example      example       example
example                         example
example

Any help would be greatly appreciated. I feel like I'm so close to the answer, but just need a little guidance. Who knows, I might be way off.

Thanks in advance!

Hi I am very new to PHP & Mysql. I am trying to insert values into two tables at the same time. One table will insert a single row and the other table will insert multiple records based on user insertion. Everything is working well, but in my second table, 1st Table ID simply insert one time and rest of the values are inserting from 2nd table itself. Now I want to insert the first table's ID Field value (auto-incrementing) to a specific column in the second table (only all last inserted rows).   Ripon.   Below is my Code:

<?php



$con = mysql_connect("localhost","root","aaa");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_select_db("ccc", $con);


$PI_No = $_POST['PI_No'];
$PO_No = $_POST['PO_No'];


$qry = "INSERT INTO wm_order_entry (
Order_No,
PI_No,
PO_No) 


VALUES(
NULL,
  '$PI_No',
 '$PO_No')";
$result = @mysql_query($qry);




$val1=$_POST['Size'];
$val2=$_POST['Style'];
$val3=$_POST['Colour'];
$val4=$_POST['Season_Code'];
$val5=$_POST['Dept'];
$val6=$_POST['Sub_Item'];
$val7=$_POST['Item_Desc'];
$val8=$_POST['UPC'];
$val9=$_POST['Qty'];
$N = count($val1);
for($i=0; $i < $N; $i++)
{


$profile_query = "INSERT INTO order_entry(Size,
Style,
Colour,
Season_Code,
Dept,
Sub_Item,
Item_Desc,
UPC,
Qty,
Order_No
) VALUES( '$val1[$i]','$val2[$i]','$val3[$i]','$val4[$i]','$val5[$i]','$val6[$i]','$val7[$i]','$val8[$i]','$val9[$i]',LAST_INSERT_ID())";
$t_query=mysql_query($profile_query);
}
header("location: WMView.php");
mysql_close($con);
?>

Output is attached.

Simple question but I couldn't find any straight forward answers.   I have a customer details table with: customer ID, name, address, email.   I also have an delivery table with delivery ID, customer ID, order ID.   How can I link the customer ID column from the customer table to the delivery table so that when changes occur on the customer table they subsequently alter all other tables?   I know it might use foreign keys but I couldn't find any online resource that explained how to use them properly. Cheers

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 

I have an existing two table structure where sub-data is related to main data in a many-to-one relationship.   There are several identically named columns in both tables.   (The PHP database class will return an associative array that did not deal with identical keys in the row.)   I'm LEFT JOINing the sub-data to the main-data tables.   When using a stand-alone query browser (to experiment), the display shows all columns, even those column names that appear more than once in the row.   Is there a general approach to making keys (column names) unique strictly using a MySQL SELECT statement?   Perhaps using table aliases, how can I concat the alias to the columns that come from each table?   What I am trying to avoid is giving an alias to each column in a list of column names, such as: SELECT C.name AS C_name C.numb AS C_numb D.name AS D_name D.numb AS D_numb etc.   I am wanting more like the result one could guess would be from: C.* AS C_* D.* AS D_*   Or better: * AS CONCAT(__TABLE__, '_', *) if __TABLE was a magic constant (like PHP's __LINE__, __FILE__, etc).  

Hi,

Stuck again with the Joomla/K2 coding.  Trying to output this field in a 2 column table but failing badly.  Any help or ideas would be greatly appreciated.

     <?php if($this->item->params->get('catItemExtraFields') && count($this->item->extra_fields)): ?>
     <!-- Item extra fields -->
     <div class="catItemExtraFields">
        <h4><?php echo JText::_('K2_ADDITIONAL_INFO'); ?></h4>
        <ul>
         <?php foreach ($this->item->extra_fields as $key=>$extraField): ?>
         <?php if($extraField->value): ?>
         <li class="<?php echo ($key%2) ? "odd" : "even"; ?> type<?php echo ucfirst($extraField->type); ?> group<?php echo $extraField->group; ?>">
            
            
               <?php if ($extraField->name =='Closing date') : ?>

                    <span class="catItemExtraFieldsLabel"><?php echo $extraField->name; ?></span>
                      <span class="catItemDateCreated"><?php echo JHTML::_('date', $this->item->publish_down ); ?></span>
                    <?php else :?>
                     <span class="catItemExtraFieldsLabel"><?php echo $extraField->name; ?></span>
                    <span class="catItemExtraFieldsValue"><?php echo $extraField->value; ?></span>
       
                    <?php endif; ?>
               
         </li>
         <?php endif; ?>
         <?php endforeach; ?>
         </ul>
       <div class="clr"></div>
     </div>
     <?php endif; ?>
 

I have a mysql table named songs with 3 columns, id, artist,title.

Most of the songs in the artist column are correct, ex:

artist
------------------
John Denver
Loretta Lynn
Shania Twain
Luke Bryan

But some of the songs in the artist column are reveresed with a comma, ex:

artist
-----------------
Dever, John
Lynn. Loretta
Twain, Shania
Bryan, Luke

Is there an easy php code snippet or mysql statement that i can use to reverse the order of first name and last name and remove the comma in the last example so the artst columd matches the first example?

I hope this makes sense, thanks, Dale.

Hello, 
I think this question is a bit of both php and mysql related. I hope I can get some help here. Especially with the mysql part. 

 

I have a table like this:

CREATE TABLE days (
    id INT AUTO_INCREMENT,
      daya INT,
      dayb INT,
    PRIMARY KEY (id)
);


INSERT INTO days VALUES
    (70, 19, 18),
    (69, 20, 17),
    (68, 24, 25),
    (67, 19, 18),   
    (66, 19, 17),    
    (65, 17, 16);

and mysql query and php snippet is this:

 $sql = "SELECT
    d1.id AS id1,
    d2.id AS id2,
    IF((d1.daya-d2.daya)=1,1,0) +
    IF((d1.daya-d2.dayb)=1,1,0) +
    IF((d1.dayb-d2.daya)=1,1,0) +
    IF((d1.dayb-d2.dayb)=1,1,0)   AS tcount
FROM days AS d1 , days AS d2
WHERE d1.id IS NOT NULL AND d2.id IS NOT NULL AND d1.id > d2.id";

  $result = $conn->query($sql);
  if ($result->num_rows > 0) {

   while($row = $result->fetch_assoc()) 
   {
        echo "<tr><td>" . $row["id1"] . "</td><td>". $row["id2"] . "</td><td>" . $row["tcount"]. 
                        "</td></tr>";   
             
    }
echo "</table>";

which gives me a table like this:

Id1  Id2   Count
50    49    1
50    48    0
49    48    0
50    47    1
49    47    1
48    47    0
50    46    1
49    46    1
48    46    0
47    46    1
50    45    1
49    45    1
48    45    0
47    45    1
46    45    1

I need to convert the table into this form: id1 goes down the first column, and id2 is in the first row.  How can i do this, please? can you give me some help? thank you!

id2    49    48    47    46    45
id1                    
50     1      0     1     1     1
49            0     1     1     1
48                  0     0     0
47                        1     1
46                              1
45                    

 

I have a table and one column has links that point to index.php?id= but I want it to display the info of that row of my database. On the index.php page i have this code Code: [Select]
<?php
include "dbaptsConfig.php";

// test id, you need to replace this with whatever id you want the result from
$id = "1";

// what you want to ask the db
$query = "SELECT * FROM `apartments` WHERE `id` = ".$id;

// actually asking the db
$res = mysql_query($query, $ms);

// recieving the answer from the db (you can only use this line if there is always only one result, otherwise will give error)
$result = mysql_fetch_assoc($res);

// if you uncomment the next line it prints out the whole result as an array (prints out the image as weird characters)
// print_r($result);

// print out specific information (not the whole array)
   echo "id: ".$result['id']."<br />";
echo "username: ".$result['username']."<br />";
   echo "type: ".$result['type']."<br />";
   echo "title: ".$result['title']."<br />";
   echo "description: ".$result['description']."<br />";  
echo "county: ".$result['county']."<br />";
   echo "town: ".$result['town']."<br />";
   echo "phone: ".$result['phone']."<br />";
   echo "rooms: ".$result['rooms']."<br />";
   echo "bath: ".$result['bath']."<br />";
   echo "squa  ".$result['square']."<br />";
echo "rent: ".$result['rent']."<br />";
echo "time: ".$result['time']."<br />";

?>I know there is something wrong with this cause I always get the same info no matter which apartment I click. Do I use the $_GET function and how do I implement this?

Does anybody know how to show the below php results in a table format?

<?php

if(isset($_POST['submit'])){
if(isset($_GET['go'])){

$fname = $_POST['fname'];
$lname = $_POST['lname'];
$skill = $_POST['skill'];
   
//connect  to the database
$db=mysql_connect  ("127.0.0.1", "root",  "") or die ('I cannot connect to the database  because: ' . mysql_error());
   
//select  the database to use
 $mydb=mysql_select_db("resource matrix");
   

//query  the database table
$sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '$fname' OR Last_Name LIKE '$lname' OR Skill_Name LIKE '$skill'";
   
//run  the query against the mysql query function
$result=mysql_query($sql);
   
//create  while loop and loop through result set
while($row=mysql_fetch_array($result)){
$First_Name  =$row['First_Name'];
$Last_Name=$row['Last_Name'];
$Resource_ID=$row['Resource_ID'];
   
//display the result of the array
echo "<ul>\n";
echo "<li>" . "<a  href=\"a.php?id=$Resource_ID\">"   .$First_Name . " " . $Last_Name .  "</a></li>\n";
echo "</ul>";
}
}
else{
echo  "<p>Please enter a search query</p>";

}
}

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";


?>

  i think this is more of a HTML problem, but it is in my PHP code so i will ask anyway!
if i have the search code:


 if (count($error) < 1) {
      $searchSQL = "SELECT id, placing, racedate, horseid FROM race WHERE ";
     
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['placing'])?"`placing` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['racedate'])?"`racedate` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['horseid'])?"`horseid` LIKE '%{$searchTermDB}%'":'';
     
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
     
      if (count($types) < 1)
         $types[] = "`placing` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
     
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " GROUP BY `horseid` ORDER BY `racedate`"; // order by title.

      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
     
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
echo '<table border="1"><tr><th>Num</th><th>Placing</th><th>Race Date</th><th>Horse ID</th></tr>';
while ($row = mysql_fetch_assoc($searchResult))
{
   echo "<tr><td>$i</td>
                    <td>{$row['placing']}</td>
                    <td><a href=\"viewrace.php?date=" . urlencode($row['racedate']) . "\">{$row['racedate']}</a></td>
                    <td>{$row['horseid']}</td></tr>";
   $i++;
}
echo '</table>';

how do i get the 'horseid' to appear at the header of the table rather than in a column?

thank you for all the brilliant help this site provides!!

I have three tables: events, orderdetails & orders.

First I query orderdetails to find all the records that match the EventID:

$query1 = SELECT * FROM orderdetails WHERE EventID = $_SESSION['EventID'];

This returns 4 records.  These 4 records have a field called DetailOrderID which is the foreign key for orders.OrderID.  Next I need to query the results of the first query to find all the records in the orders table that match up.  For example:

SELECT * from orders where $query1.DetailOrderID = orders.OrderID.

How would I go about doing this?  I'm head down the temporary table solution but wanted to through this one out for discussion before I invest too much time.

Hi

I have the following code:

Code: [Select]
$result = mysql_query("SELECT * FROM xbox_games order by gameid");
$row = mysql_fetch_assoc($result);
$id = $row["gameid"];
$title = $row["gametitle"];
$cover = $row["cover"];

                                <?php echo $title;?>

Which displays only the first result from my database.

How can i change this to display all the results either as a list, or in a table?

Thanks

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