Select specific "ID" column from a MYSQL join?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Select specific "ID" column from a MYSQL join?

Post by TheBrandon »

Hello all,

I'm working on a theatre's website where members can be assigned to groups. Each table has its own ID column. Here is my PHP:

Code: Select all

 
function SearchByGroupNumber($string){
    $sql = "SELECT DISTINCT * FROM groups, group_members, users, member_personal WHERE groups.id LIKE '$string%' AND groups.id = group_members.groupid AND group_members.linkid = users.id AND users.id = member_personal.linkid ORDER BY groups.id ASC";
    $query = mysql_query($sql) or die(mysql_error());
    $total = mysql_num_rows($query);
 
    if($total>0) {
    
        $i=0;
    
        while($i < $total){
 
            
            $row_sql = mysql_fetch_assoc($query);
            echo $row_sql['id'];
 
The code is incomplete of course, but basically how do I get $row_sql['id']; to pull a specific ID, like groups.id?

What it's doing is joining them all together, then picking the "last" ID field available.
thk2551985
Forum Newbie
Posts: 2
Joined: Mon Jul 21, 2008 7:01 am

Re: Select specific "ID" column from a MYSQL join?

Post by thk2551985 »

HI :)

Rather calling the particular column by name, call like this :

$row_sql[0]

That is, rather than using associative array by calling through column name just call by the number of column starting by zero.


Thanks!
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select specific "ID" column from a MYSQL join?

Post by TheBrandon »

Hmm, I tried that but I don't get any result? I tried 0 through 5.

Like this:

Code: Select all

 
echo $row_sql[0];
echo $row_sql[1];
echo $row_sql[2];
echo $row_sql[3];
echo $row_sql[4];
echo $row_sql[5];
 
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Select specific "ID" column from a MYSQL join?

Post by califdon »

What we need to see is your database schema (the fields in each table). Your query is hard to understand without knowing what each table consists of. I'm inclined to think that your basic data structure may be incorrect, but I can't tell without seeing what's actually in each table. Every table should clearly represent an entity and all of its attributes. Tables named group_members, users, and member_personal doesn't sound like distinct entities to me, at least at first glance. If your tables are not properly structured, you will forever have problems trying to join them and extract the data you need.

The preferred way to present your schema is like this:

Code: Select all

[b]Groups:[/b]
    id
    groupName
    ... (your other fields)
 
[b]group_members[/b]:
    groupid
    linkid
    ... (your other fields)
 
etc.
miro_igov
Forum Contributor
Posts: 485
Joined: Fri Mar 31, 2006 5:06 am
Location: Bulgaria

Re: Select specific "ID" column from a MYSQL join?

Post by miro_igov »

If more than 1 table contains column named id you will get the instance of the last joined table with column named id. So in your select add

Code: Select all

...., groups.id AS groups_id ....
and then search for it in $row_sql['groups_id'];
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Select specific "ID" column from a MYSQL join?

Post by TheBrandon »

Sorry for the late reply.

My SQL schema is:

Code: Select all

 
-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Jun 23, 2009 at 01:40 PM
-- Server version: 5.0.27
-- PHP Version: 5.2.0
-- 
-- Database: `login`
-- 
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `active_guests`
-- 
 
CREATE TABLE `active_guests` (
  `ip` varchar(15) NOT NULL,
  `timestamp` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY  (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `active_users`
-- 
 
CREATE TABLE `active_users` (
  `username` varchar(30) NOT NULL,
  `timestamp` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY  (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `banned_users`
-- 
 
CREATE TABLE `banned_users` (
  `username` varchar(30) NOT NULL,
  `timestamp` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY  (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `group_members`
-- 
 
CREATE TABLE `group_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `linkid` int(11) NOT NULL,
  `groupid` int(11) NOT NULL,
  `control` tinyint(4) DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `groups`
-- 
 
CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` mediumtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `member_numbers`
-- 
 
CREATE TABLE `member_numbers` (
  `id` smallint(5) NOT NULL AUTO_INCREMENT,
  `membernum` varchar(2000) character SET utf8 collate utf8_unicode_ci DEFAULT NULL,
  `linkid` varchar(2000) character SET utf8 collate utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1601 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `member_personal`
-- 
 
CREATE TABLE `member_personal` (
  `id` smallint(3) NOT NULL AUTO_INCREMENT COMMENT 'Automated ID',
  `first_name` varchar(40) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Billing First Name',
  `last_name` varchar(40) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Billing Last Name',
  `billing_street` varchar(40) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Billing Street',
  `billing_city` varchar(40) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Billing City',
  `billing_state` tinytext character SET utf8 collate utf8_unicode_ci NOT NULL,
  `billing_zip` smallint(5) NOT NULL COMMENT 'Billing Zipcode',
  `billing_phone` varchar(30) character SET utf8 collate utf8_unicode_ci NOT NULL,
  `email` varchar(40) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Confirmation Email',
  `credit_card_name` varchar(80) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Name on Credit Card',
  `credit_card_number` smallint(12) NOT NULL COMMENT 'Credit Card Number',
  `user_log` varchar(40) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Who added this info',
  `linkid` smallint(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `performances`
-- 
 
CREATE TABLE `performances` (
  `id` tinyint(255) NOT NULL AUTO_INCREMENT,
  `show_id` tinyint(255) NOT NULL,
  `perf_id` tinyint(255) NOT NULL,
  `perf_date` varchar(255) character SET utf8 collate utf8_unicode_ci NOT NULL,
  `perf_time` varchar(255) NOT NULL,
  `tickets_avail` varchar(50) NOT NULL,
  `ticket_cost` tinyint(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `shows`
-- 
 
CREATE TABLE `shows` (
  `id` smallint(3) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Automated ID',
  `show_name` varchar(60) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Name of the Show',
  `show_date` varchar(30) NOT NULL COMMENT 'Date of the Show',
  `tickets_available` smallint(3) NOT NULL COMMENT 'Total Tickets Allotted to Website',
  `tickets_left` smallint(3) NOT NULL COMMENT 'Tickets Remaining',
  `show_time` varchar(30) character SET utf8 collate utf8_unicode_ci NOT NULL,
  `ticket_cost` tinyint(10) NOT NULL,
  `user_log` varchar(40) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Who added this show',
  PRIMARY KEY  (`id`),
  KEY `show_name` (`show_name`,`show_date`,`tickets_available`,`tickets_left`,`show_time`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `ticket_sales`
-- 
 
CREATE TABLE `ticket_sales` (
  `id` smallint(3) NOT NULL AUTO_INCREMENT COMMENT 'Automated ID',
  `mid` smallint(3) NOT NULL COMMENT 'Member Ticket ID',
  `mnum` mediumint(255) NOT NULL,
  `method` smallint(3) NOT NULL COMMENT 'Reserved, Purchased, Will Call',
  `ticket_amount` smallint(3) NOT NULL COMMENT 'How many tickets have they reserved?',
  `tickets_left` smallint(3) NOT NULL COMMENT 'How many tickets can they reserve?',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Date of transaction',
  `epoch` varchar(30) NOT NULL,
  `user_log` varchar(40) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Who caused the transaction?',
  `show_id` smallint(3) NOT NULL COMMENT 'Show the tickets are for',
  `perf_id` mediumtext character SET utf8 collate utf8_unicode_ci NOT NULL,
  `total` varchar(10) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Total owed, if any (Depends on method)',
  `seating` varchar(10) character SET utf8 collate utf8_unicode_ci DEFAULT NULL COMMENT 'Seat selection (to be added later)',
  `confirmation_number` varchar(100) character SET utf8 collate utf8_unicode_ci NOT NULL COMMENT 'Confirmation Number',
  PRIMARY KEY  (`id`),
  KEY `mid` (`mid`,`ticket_amount`,`tickets_left`,`show_id`),
  KEY `mnum` (`mnum`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;
 
-- --------------------------------------------------------
 
-- 
-- Table structure for table `users`
-- 
 
CREATE TABLE `users` (
  `id` smallint(255) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `password` varchar(32) DEFAULT NULL,
  `userid` varchar(32) DEFAULT NULL,
  `userlevel` tinyint(1) UNSIGNED NOT NULL,
  `memberlevel` tinyint(10) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `timestamp` int(11) UNSIGNED NOT NULL,
  `current` tinyint(2) NOT NULL DEFAULT '0',
  `member_num` varchar(5000) character SET utf8 collate utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`,`password`,`userid`,`userlevel`,`memberlevel`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
 
I'm still trying to find a solution for this problem if anyone can help.
Post Reply