How to Get VALUES from another table into another table in PHP/MySQL

Question

First I should say my English is not perfect. I am using XAMPP 5.6.0.0

I have two tables, services and services_cat.

    -- Table structure for table `services_cat`

    CREATE TABLE IF NOT EXISTS `services_cat` 
   (`ser_cat_id` int(11) NOT NULL,
    `service_category` varchar(50) NOT NULL)

    -- Table structure for table `services`

    CREATE TABLE IF NOT EXISTS `services` 
    (`service_id` int(11) NOT NULL,
     `scid` int(11) NOT NULL,
     `service_title` varchar(50) NOT NULL,
     `service_statement` varchar(1000) NOT NULL,
     `service_photo` varchar(100) NOT NULL)

I want to show following data in one table.

service_id
ser_cat_id
service_category
service_title
service_statement

ser_cat_id and service_id are PRIMARY KEYs.

here ser_cat_id save into scid in the services table. This ser_cat_id save multiple times in the scid colomn. But service_title will be changed. There are different types of service_title, but the scid is same for that different service_titles. That scid comming from ser_cat_id from services_cat table.

I can pass ser_cat_id to scid from service_cat table and I can fetch (display) that scid. But I want to display that DISTINCT service_category name of the ser_cat_id(same as scid in services table).

Can you please help me ..

Following is my code.

    <?php
    include_once("conn.php");

    $sql = mysql_query("SELECT * FROM services_cat JOIN services ON services_cat.ser_cat_id = services.service_id GROUP BY service_category ");
    while($row = mysql_fetch_object($sql))
    {

        echo "<tr>
        <td><p class='table-p'>$row->scid</p></td>
        <td><p class='table-p'>$row->ser_cat_id</p></td>
        <td><p class='table-p'>$row->service_category = $row->scid</p></td>
        <td><p class='table-p'>$row->service_title</p></td>
        <td><p class='table-p'>$row->service_statement</p></td>

        <td><p class='table-p'><a href='service_edit.php?ide=$row->service_id'>Edit</a> |
        <a href='service_delete.php?idd=$row->service_id'>Delete</a></p></td>
        ";
    }
    ?>

one ser_cat_id may have several service_category ? otherwise your query is correct. I believe you want to recieve DISTINCT service_title not service_category — M0rtiis
– M0rtiis, Commented Aug 17, 2015 at 13:02
ser_cat_id have many service_category Ex :: ser_cat_id = 1 service_category = Decoration ,,,,,, ser_cat_id = 2 service_category = Dressing ...... ser_cat_id = 3 service_category = Dancing etc.. — Random.dmx
– Random.dmx, Commented Aug 17, 2015 at 13:15
ser_cat_id is the primary key of services_cat. scid is the related foreign key in services table. All joins between these tables should use ON services_cat.ser_cat_id = services.scid, as @Berriel said. So, any joined query you do will have the same values for those two fields. — James
– James, Commented Aug 17, 2015 at 13:18
No .. Output table is not the DB table. Its temporary table to Display Data from the DB tables. — Random.dmx
– Random.dmx, Commented Aug 17, 2015 at 13:23

Berriel · Accepted Answer · 2015-08-17 14:10:23Z

1

Based on what you want, this is what you need:

SELECT s.service_id, c.ser_cat_id, c.service_category, s.service_title, s.service_statement 
FROM services_cat AS c 
INNER JOIN services AS s 
  ON c.ser_cat_id = s.scid

Check this running example: http://sqlfiddle.com/#!9/013f7/3/0

Inside your loop, your $row will have this data, as you need.

$row['service_id']
$row['ser_cat_id']
$row['service_category']
$row['service_title']
$row['service_statement']

answered Aug 17, 2015 at 14:10

Berriel

13.8k4 gold badges51 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Random.dmx Over a year ago

OUTSTANDING PERFORMANCE Sir Berriel !!!!!! You are the GREAT... WoooooooooooooW Thats what I Need .... IT's Working ..... Outstanding ..... You are Really Great ..... Thank You Sooooooo Much :) :) :) I get ideas what i missed in my code .. Again 1000 times Thanks :)

user4628565 · Accepted Answer · 2015-08-17 13:12:31Z

0

try to change,

$sql = mysql_query("SELECT * FROM services_cat JOIN services ON services_cat.ser_cat_id = services.service_id GROUP BY service_category ");

to

$sql = mysql_query("SELECT * FROM services_cat JOIN services ON services_cat.ser_cat_id = services.scid GROUP BY service_category ");

answered Aug 17, 2015 at 13:12

user4628565

1 Comment

Random.dmx Over a year ago

Then while loop is not working :( .. Only Display 04 items according to service_category. Because service_category have 04 values.

Collectives™ on Stack Overflow

How to Get VALUES from another table into another table in PHP/MySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related