Php help

[Waq158]

I want to display information about phone companies including what phones they offer and price plans from my database. The way I went about it is by creating 3 table in the database PhoneCompany, Phones, PricePlans.

But now I'm stuck on how to display this. This is how far I've got:

 

<form action="" method=post>
<table><tr><td>Phone Company</td></tr>
<tr><td>Select Company:<select size="1" id="phonecompany" name="phonecompany"><?php
  $gather = mysql_query("SELECT * FROM phonecompany ORDER BY RAND()");
  while($object=mysql_fetch_object($gather)){
    echo "<option value='$object->id'>$object->name</option>";  } ?> 
</td></tr><tr><td>
<input name="select" type="submit" id="select" value="Select">
</table></form>

 

Now how do I get the other 2 tables to show up. I know how to get the information from the database and all just not sure how to display it, if that makes any sense.

 

Thanks in advance.

Waq.

 

[Psycho]

I would expect there is a relationship between the three tables - which you have not provided. You would want to do a single query that JOINs the tables to get all the data you want. Please specify how the tables are releated (i.e. what fields are available in each and identify the foreign keys) and detail how you want the information displayed. We can then help in constructing the appropriate query.

[Waq158]

--
-- Table structure for table `phonecomapny`
--

CREATE TABLE `phonecompany` (
  `id` int(2) NOT NULL auto_increment,
  `owner` varchar(40) collate latin1_general_ci NOT NULL,
  `companyname` varchar(100) collate latin1_general_ci NOT NULL,
  `image` varchar(200) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `phones`
--

CREATE TABLE `phones` (
  `id` int(2) NOT NULL auto_increment,
  `owner` varchar(40) collate latin1_general_ci NOT NULL,
  `phonename` varchar(100) collate latin1_general_ci NOT NULL,
  `image` varchar(200) collate latin1_general_ci NOT NULL default '',
  `price` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;


-- --------------------------------------------------------

--
-- Table structure for table `priceplan`
--

CREATE TABLE `priceplan` (
  `id` int(2) NOT NULL auto_increment,
  `owner` varchar(40) collate latin1_general_ci NOT NULL,
  `planname` varchar(100) collate latin1_general_ci NOT NULL,
  `minutes` varchar(100) NOT NULL default '',
  `price` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

 

As you can see they all have 'owner' field which joins them. I could add a 'phonecompany' field?

[Waq158]

Any ideas guys?

[Waq158]

I was thinking maybe not make it a form instead make it automatically select when i click on it and make it fetch data from the other table and put them in a another drop down menu? not sure if any of that made sense to anyone

[Psycho]

You should use the auto-increment "id" field from the company table to link all the records. I would suggest renaming the field to "company_id" and use that in all the tables. So, I would also assume that the the phones and plans only associate back to the companies table - i.e. there is no direct association between phones and planes. In other words, a companies plans are available for all phone models. If not, you will need to create an association between plans and the phones. Since it would be a many-to-many relationship you would need a fourth table to make the associations.

 

The one thing you didn't provide is a description of how you want to output the data. Do you want to show every plan for every phone? Assuming you do, here is one possible solution (I am using "company_id" as the auto-in in the company table and the associative field in the phone and plans tables).

 

This is just a rough example and has not been tested

<?php

$query = "SELECT co.companyname, co.image as company_image,
                 ph.phonename, ph.image as phone_image, ph.price as phone_price
                 pp.planname, pp.minutes, pp.price as plan_price
          FROM `phonecompany` co
          JOIN `phones` ph using(`company_id`)
          JOIN `priceplan` pp using(`company_id`)
          ORDER BY co.companyname, ph.phonename";
$result = mysql_query($query);

$output = '';
$current_company = '';
$current_phone = '';
while($record = mysql_fetch_assoc($result))
{
    //Display company name header
    if($current_company != $record['companyname'])
    {
        $current_company = $record['companyname'];
        $output .= "<tr><th colspan=\"2\">";
        $output .= "<img src=\"{$record['company_image']}\" />{$record['companyname']}";
        $output .= "</th></tr>\n";
    }
    //Display phone
    if($current_phone != $record['phonename'])
    {
        $current_phone = $record['phonename'];
        $output .= "<tr>";
        $output .= "<td><img src=\"{$record['phone_image']}\" /></td>";
        $output .= "<td>{$record['phonename']}<br />{$record['phone_price']}</td>";
        $output .= "</tr>\n";
        $output .= "<tr>";
        $output .= "<td> </td>";
        $output .= "<td>Available Plans</td>";
        $output .= "</tr>\n";
    }
    //Display plans
    $output .= "<tr>";
    $output .= "<td> </td>";
    $output .= "<td>{$record['planname']}: Minutes{$record['minutes']}} ({$record['plan_price']})</td>";
    $output .= "</tr>\n";
}
?>
<html>
<body>
<table>
<?php echo $output; ?>
</table>
</body>
</html>

[Waq158]

Thanks for that unfortunately it comes up as a blank screen using the code provided. Apreciated nonetheless.

What I wanted was for the user to select a company and all the phones and price plans to appear in a dropdown style menu so they could select and buy them. Oh and the phones and plans are not linked.

[Psycho]

As I plainly stated above:

 

1) That code assumes you have implemented company_id as the autoint field for the company table and used the same field name in the two other tables to associate the records. If you have not done that then it obviously won't work.

 

2) That code has not been tested.

 

Also, that code will display all the phonen companies and list all of their phones and each phone option under each phone. Your original post said nothing about how you wanted the results displayed. Then in your last post you throw in different requirements about the user selecting a phone company and then geting different options in a select list to chooses from. None of that is extremely difficult and the sample code I provided should give you the correct direction to go. I am not going to write this whole thing for you for free. Either make an attempt to write it yourself (and seek help where you have problems) or post in the freelancing forum to find someone to do it for you for money.

[Waq158]

Don't worry figured out how to do it. thanks anyways