Page 1 of 1

Select specific "ID" column from a MYSQL join?

Posted: Fri Jun 12, 2009 2:34 pm
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.

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

Posted: Fri Jun 12, 2009 2:43 pm
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!

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

Posted: Fri Jun 12, 2009 2:53 pm
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];
 

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

Posted: Sat Jun 13, 2009 12:49 pm
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.

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

Posted: Sun Jun 14, 2009 7:30 am
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'];

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

Posted: Tue Jun 23, 2009 1:41 pm
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.