PHP - Moved: How To Retrieve Correct Number Of Records From 3 Tables

I have a db with 4 tables:
table_A
table_B
table_C
table_D


table_A contains personal info about users and has a primary id field called uid.
Each of tables B, C and D is linked to table_A via the foreign key f_uid.

table_A also has a column called signed_up which can contain a 1 or a 0.

I want to pull up lots of info from each of the 4 tables for every user, as long as the signed_up column in table_A contains a 1.

So, I've made a query like this:

Code: [Select]
$q = "SELECT
table_A . col_1 ,
table_A . col_2 ,
table_A . col_3 ,
table_B . col_1 ,
table_B . col_2 ,
table_B . col_3 ,
table_C . col_1 ,
table_C . col_2 ,
table_C . col_3 ,
table_D . col_1 ,
table_D . col_2 ,
table_D . col_3
FROM
table_A, table_B, table_C, table_D
WHERE
table_A . uid = table_B . f_uid
AND
table_A . uid = table_C . f_uid
AND
table_A . uid = table_D . f_uid
AND
table_A . signed_up = '1'
ORDER BY
table_A . some_col
";
$r = mysqli_query($dbc, $q);
The query is executing but it's also pulling up records where signed_up in table_A contains a '0'. Can anyone see a logical flaw in my query?...

Alternatively, is there an easier way I could build the query?

TIA

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=325939.0

It's pretty simple to see what I am trying to do here. For some reason all results in the table are the same exact cityName replacing all existing records. The echoed results are correct. I've include a small dump of my table as well.

$query = "SELECT cityName FROM sys_city_dev_2";
      $resource = mysqli_query($cxn, $query) or die("MySQL error: " . mysqli_error($cxn) . "<hr>\nQuery: $query");
      
  while($result = mysqli_fetch_assoc($resource)) {

          $nox = $result['cityName'];

          $toUpper = ucfirst($nox);
  echo "$toUpper" . "<br />";

          $setString = "UPDATE sys_city_dev_2 SET cityName = '" . $toUpper ."' WHERE cityName != ''";
          mysqli_query($cxn,$setString);


      } 

100 Records of table dump (pre running my script above):

--
-- Table structure for table `sys_city_dev_2_backup`
--

CREATE TABLE IF NOT EXISTS `sys_city_dev_2_backup` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Mid` int(11) NOT NULL DEFAULT '0',
  `cityName` varchar(30) NOT NULL DEFAULT '',
  `forder` int(4) NOT NULL DEFAULT '0',
  `disdplay` int(4) NOT NULL DEFAULT '0',
  `cid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=113970 ;

--
-- Dumping data for table `sys_city_dev_2_backup`
--

INSERT INTO `sys_city_dev_2_backup` (`ID`, `Mid`, `cityName`, `forder`, `disdplay`, `cid`) VALUES
(84010, 1, 'dothan', 0, 0, 0),
(84011, 1, 'alabaster', 0, 0, 0),
(84012, 1, 'birmingham', 0, 0, 0),
(84013, 2, 'flagstaff', 0, 0, 0),
(84014, 1, 'auburn', 0, 0, 0),
(84015, 1, 'florence', 0, 0, 0),
(84016, 1, 'gadsden', 0, 0, 0),
(84017, 1, 'huntsville', 0, 0, 0),
(84018, 1, 'mobile', 0, 0, 0),
(84019, 1, 'montgomery', 0, 0, 0),
(84020, 1, 'tuscaloosa', 0, 0, 0),
(84021, 2, 'mohave valley', 0, 0, 0),
(84022, 2, 'phoenix', 0, 0, 0),
(84023, 2, 'prescott', 0, 0, 0),
(84024, 2, 'sierra vista', 0, 0, 0),
(84025, 2, 'tucson', 0, 0, 0),
(84026, 2, 'yuma', 0, 0, 0),
(84027, 3, 'bakersfield', 0, 0, 0),
(84028, 3, 'chico', 0, 0, 0),
(84029, 3, 'fresno / madera', 0, 0, 0),
(84030, 3, 'gold country', 0, 0, 0),
(84031, 3, 'humboldt county', 0, 0, 0),
(84032, 3, 'imperial', 0, 0, 0),
(84033, 3, 'inland empire', 0, 0, 0),
(84034, 3, 'los angeles', 0, 0, 0),
(84035, 3, 'alhambra', 0, 0, 0),
(84036, 3, 'merced', 0, 0, 0),
(84037, 49, 'fayetteville', 0, 0, 0),
(84038, 49, 'fort smith', 0, 0, 0),
(84039, 49, 'jonesboro', 0, 0, 0),
(84040, 49, 'little rock', 0, 0, 0),
(84041, 49, 'arkadelphia', 0, 0, 0),
(84042, 49, 'texarkana', 0, 0, 0),
(84043, 3, 'modesto', 0, 0, 0),
(84044, 3, 'alta sierra', 0, 0, 0),
(84045, 3, 'alpine', 0, 0, 0),
(84046, 3, 'pedley', 0, 0, 0),
(84047, 3, 'redding', 0, 0, 0),
(84048, 3, 'alondra park', 0, 0, 0),
(84049, 3, 'sacramento', 0, 0, 0),
(84050, 4, 'canon city', 0, 0, 0),
(84051, 3, 'san luis obispo', 0, 0, 0),
(84052, 3, 'santa barbara', 0, 0, 0),
(84053, 3, 'stockton', 0, 0, 0),
(84054, 3, 'aliso viejo', 0, 0, 0),
(84055, 3, 'visalia', 0, 0, 0),
(84056, 3, 'yuba city', 0, 0, 0),
(84057, 4, 'boulder', 0, 0, 0),
(84058, 4, 'colorado springs', 0, 0, 0),
(84059, 4, 'denver', 0, 0, 0),
(84060, 4, 'applewood', 0, 0, 0),
(84061, 4, 'pueblo', 0, 0, 0),
(84062, 4, 'air force academy', 0, 0, 0),
(84063, 5, 'avon', 0, 0, 0),
(84064, 5, 'hartford', 0, 0, 0),
(84065, 5, 'new haven', 0, 0, 0),
(84066, 5, 'ansonia', 0, 0, 0),
(84067, 5, 'fairfield', 0, 0, 0),
(84068, 7, 'daytona beach', 0, 0, 0),
(84069, 7, 'sebastian', 0, 0, 0),
(84070, 5, 'wallingford center', 0, 0, 0),
(84071, 8, 'belvedere park', 0, 0, 0),
(84072, 7, 'sarasota springs', 0, 0, 0),
(84073, 7, 'sandalfoot cove', 0, 0, 0),
(84074, 7, 'san carlos park', 0, 0, 0),
(84075, 7, 'st. augustine', 0, 0, 0),
(84076, 7, 'tallahassee', 0, 0, 0),
(84077, 7, 'safety harbor', 0, 0, 0),
(84078, 7, 'ruskin', 0, 0, 0),
(84079, 8, 'athens-clarke county', 0, 0, 0),
(84080, 8, 'atlanta', 0, 0, 0),
(84081, 8, 'augusta-richmond county', 0, 0, 0),
(84082, 8, 'brunswick', 0, 0, 0),
(84083, 8, 'columbus', 0, 0, 0),
(84084, 8, 'americus', 0, 0, 0),
(84085, 8, 'acworth', 0, 0, 0),
(84086, 8, 'valdosta', 0, 0, 0),
(84087, 10, 'boise', 0, 0, 0),
(84088, 10, 'ammon', 0, 0, 0),
(84089, 10, 'moscow', 0, 0, 0),
(84090, 10, 'blackfoot', 0, 0, 0),
(84091, 10, 'twin falls', 0, 0, 0),
(84092, 10, 'meridian', 0, 0, 0),
(84093, 10, 'jerome', 0, 0, 0),
(84094, 10, 'idaho falls', 0, 0, 0),
(84095, 11, 'addison', 0, 0, 0),
(84096, 10, 'garden city', 0, 0, 0),
(84097, 10, 'eagle', 0, 0, 0),
(84098, 10, 'chubbuck', 0, 0, 0),
(84099, 10, 'caldwell', 0, 0, 0),
(84100, 12, 'bloomington', 0, 0, 0),
(84101, 12, 'evansville', 0, 0, 0),
(84102, 12, 'fort wayne', 0, 0, 0),
(84103, 12, 'indianapolis', 0, 0, 0),
(84104, 12, 'muncie / anderson', 0, 0, 0),
(84105, 12, 'lafayette / west lafayette', 0, 0, 0),
(84106, 12, 'south bend / michiana', 0, 0, 0),
(84107, 12, 'terre haute', 0, 0, 0),
(84108, 12, 'northwest indiana', 0, 0, 0),
(84109, 13, 'ames', 0, 0, 0);

I wonder whether someone can help me please.

I've found http://www.plus2net.com/php_tutorial/ajax-listbox.php tutorial to create a drop down menu using mySQL table data, which, in turn returns a list of results on the page.

Following this tutorial I've put together the tables in my database and the required scripts as shown in the tutorial with the one exception, the "z_db.php" file, which I've assumed to be:

Code: [Select]
<?php mysql_connect("host", "user", "password")or die(mysql_error()); mysql_select_db("database"); ?>

The problem I have, is that when I try and run this, I receive the following error: Quote

Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in /homepages/2/d333603417/htdocs/development/catsearch.php on line 91 which is this line in the search form: echo "</head><body onload="ajaxFunction()";>";.


I must admit I've guessed as to the structure of the 'z_db.php' file should look like because this is not shown so perhaps this is the problem.

I just wondered wether someone could perhaps take a look at this please and let me know where I've gone wrong.


Many thanks and kind regards

Hi guys,

Currently I am editing profiles online and get it updated in my database.

And when user has successfully updated his/her profile, they will be directed to a link
echo '<p>Your profile has been successfully updated. Would you like to <a href="viewprofile.php?tutor_id=' . $_GET[$row['tutor_id']] . '">view your profile</a>?</p>';

As you can see, I have set tutor_id=' . $_GET[$row['tutor_id']] . ', which I personally know is incorrect to code it in this manner. May I know how do I code in a way where I can achieve something like this...
www.abc.com/viewprofile?tutor_id=12343


The value which currently I receive is www.abc.com/viewprofile?tutor_id='blank'
Code: [Select]
<?php
if (!$error) {
      if (!empty($tutor_id) && !empty($name) && !empty($nric) && !empty($dob_day) && !empty($dob_mth) && !empty($dob_year) && !empty($gender)) {
        
// Only set the picture column if there is a new picture
if (!empty($new_picture)) {
$query = "UPDATE tutor_profile SET name = '$name', nric = '$nric', dob_day = '$dob_day', dob_mth = '$dob_mth', dob_year = '$dob_year',  
gender = '$gender', picture = '$new_picture' WHERE tutor_id = '$tutor_id' ";
}
else {
          $query = "UPDATE tutor_profile SET name = '$name', nric = '$nric', dob_day = '$dob_day', dob_mth = '$dob_mth', dob_year = '$dob_year',  
  gender = '$gender' WHERE tutor_id = '$tutor_id' ";
        }

mysqli_query($dbc, $query)
or die(mysqli_error($dbc));

        // Confirm success with the user
        echo '<p>Your profile has been successfully updated. Would you like to <a href="viewprofile.php?tutor_id=' . $_GET['tutor_id'] . '">view your profile</a>?</p>';

        mysqli_close($dbc);
        exit();
?>