Select query not working

zed420 · October 17, 2008

Hi All

I wonder if someone can help me, I’m trying to build a site for a taxi company, I’m trying to use a select query where a Registered User can book a taxi on line and if he wishes to view these bookings he can do so. These are my tables and the select query I’m using is;

$query = "SELECT * FROM blockBook, user

WHERE blockBook.user_id = user.id";

$result = mysql_query($query)or die(mysql_error());

DROP TABLE user;

CREATE TABLE `user` (

`id` smallint(5) unsigned NOT NULL auto_increment,

`fullname` varchar(60) NOT NULL,

`username` char(15) NOT NULL,

`pswd` char(32) NOT NULL,

`address` varchar(70) NOT NULL,

`post_code` varchar(10) NOT NULL,

`tel` int(11) NULL,

`mobile` char(40) NOT NULL,

`email` char(55) NOT NULL,

`profile` char(255) NOT NULL,

PRIMARY KEY (`id`));

DROP TABLE job_tb;

CREATE TABLE `job_tb` (

`job_id` tinyint(4) NOT NULL auto_increment,

`user_id` int default NULL,

`typeOfbooking` char(10) default NULL,

`typeOfcustomer` char(10) default NULL,

`typeOfvehicle` char(20) default NULL,

`noOfVehicle` char(10) default NULL,

`regul` char(20) default NULL,

`mon` char(5) default NULL,

`tue` char(5) default NULL,

`wed` char(5) default NULL,

`thur` char(5) default NULL,

`fri` char(5) default NULL,

`sat` char(5) default NULL,

`sun` char(5) default NULL,

`cust_name` varchar(40) NOT NULL,

`cust_address` varchar(60) NOT NULL,

`post_code` varchar(12) default NULL,

`cust_tel` int(11) NOT NULL,

`email` varchar(60) NOT NULL,

`des` varchar(50) NOT NULL,

`reqVehicle` char(10) default NULL,

`dateDay` tinyint(2) default NULL,

`dateMO` tinyint(12) default NULL,

`dateYr` mediumint(5) default NULL,

`timeHr` tinyint(2) default NULL,

`timeMin` tinyint(2) default NULL,

`comment` varchar(255) default NULL,

PRIMARY KEY (`job_id`),

FOREIGN KEY (user_id) REFERENCES user(id)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

There are no errors but it’s not bringing up with any results. Any suggestions ???

Thanks

Zed

rhodesa · October 17, 2008

in your query, you reference blockBook, but you post the CREATE TABLE for job_tb. can you clarify on that? also, are you sure there is data in the tables?

zed420 · October 17, 2008

I'm sorry I posted the wrong table there. These are the right tables and yes there is data there. When i look into database in blockBook table it shows 0 vlue for user_id if thats any clue to the problem.

$query = "SELECT * FROM blockBook, user

WHERE blockBook.user_id = user.id";

$result = mysql_query($query)or die(mysql_error());

DROP TABLE user;

CREATE TABLE `user` (

`id` smallint(5) unsigned NOT NULL auto_increment,

`fullname` varchar(60) NOT NULL,

`username` char(15) NOT NULL,

`pswd` char(32) NOT NULL,

`address` varchar(70) NOT NULL,

`post_code` varchar(10) NOT NULL,

`tel` int(11) NULL,

`mobile` char(40) NOT NULL,

`email` char(55) NOT NULL,

`profile` char(255) NOT NULL,

PRIMARY KEY (`id`));

DROP TABLE blockBook;

CREATE TABLE `blockBook` (

`job_id` smallint(5) unsigned NOT NULL auto_increment,

`typeOfcustomer` char(10) default NULL,

`typeOfvehicle` char(20) default NULL,

`noOfVehicle` char(10) default NULL,

`regul` char(20) default NULL,

`cust_name` varchar(40) NOT NULL,

`cust_address` varchar(60) NOT NULL,

`post_code` varchar(12) default NULL,

`cust_tel` int(11) NOT NULL,

`des` varchar(50) NOT NULL,

`dateDay` tinyint(2) default NULL,

`dateMO` tinyint(12) default NULL,

`dateYr` mediumint(5) default NULL,

`timeHr` tinyint(2) default NULL,

`timeMin` tinyint(2) default NULL,

`user_id` int(4) default NULL,

PRIMARY KEY (`job_id`),

FOREIGN KEY (user_id) REFERENCES user(id)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Maq · October 17, 2008

This could also have something to do with how you're displaying the results, code? Try running this query in PhpMyAdmin or w/e you use.

rhodesa · October 17, 2008

No, it's because you are doing an INNER JOIN. So if blockBook.user_id isn't in the user table, it won't be returned. If you want ALL blockBook records regardless of the user existing, try this:

$query = "SELECT *  FROM blockBook LEFT JOIN user ON blockBook.user_id = user.id";
$result = mysql_query($query)or die(mysql_error());

p.s. - when posting code, put it inside CODE tags. it's the button with the # sign

zed420 · October 17, 2008

Thanks rhodesa, there's (id) in the user table and blockBook.user_id is the reference of it in blockBook table of course.

rhodesa · October 17, 2008

right, but you said the value of blockBook.user_id is 0 for all your records correct? that means whatever is populating your blockBook table isn't putting the user_id in properly.

Maq · October 17, 2008

You're query is fine now, it's the part of your script where you add the "id" to the "blockBook".

zed420 · October 18, 2008

Yeah I think this is where the real difficulty is, I'm not so sure how to populate this user_id in blockBook table.

Any suggestion would be greatly appreicated.

Zed

Maq · October 19, 2008

How are you populating the table?

zed420 · October 19, 2008

By posting a HTML form;

   ]<form name="blockBook" method="post" action="<?php echo $PHP_SELF ?>">
<table width="100%" border="0" cellspacing="1" cellpadding="0">
  <tr>
    <td width="13%">Customer/Acc no.</td>
    <td width="14%"><input name="user_id" type="text" id="user_id" size="10" /></td>
  </tr>
  <tr>
    <td width="13%"></td>
    <td width="14%"><input name="Job_id" type="hidden" id="id" size="40" /></td>
  </tr>
  <tr>
    <td>Type of Customer:</td>
    <td> <select name="typeOfcustomer" onchange="accountCust('parent',this,0)">
        <option value="cash"  selected="selected">Cash</option>
        <option value="authAc.php">Account</option>
      </select></td>
    <td width="6%">Date:</td>
    <td width="22%"><?php
/********************************   Dropdown box for Date  ******************/

  $today = Time();                     //stores today's date
  $f_today = date("d-M-Y",$today);     //formats today's date

   echo "<div align='center'>\n"; 
  echo "<div align='center'>\n"; 

  /* create form containing date selection list */
  //echo "<form action='processform.php' method='post'>\n";

  /* build selection list for the day */
  $todayDay= date("d",$today);    //get the day from $today
  echo "<select name='dateDay' > \n";
  for ($n=1;$n<=31;$n++)  
  {
    echo " <option value=$n";
    if ($todayDay == $n ) 
    {
      echo " selected";
    }
    echo "> $n\n";
  }
  echo "</select>";

  /* create an array of months*/
  $monthName = array(1=> "January", "February", "March", "April",
                         "May", "June", "July", "August",
                         "September", "October", "November",
                         "December");

  /* build selection list for the month */
  $todayMO = date("m",$today);             //get the month from $today
  echo "<select name='dateMO' >\n";
  for ($n=1;$n<=12;$n++) 
  {
    echo "<option value=$n\n";
    if ($todayMO == $n)
    {
      echo " selected";
    }
    echo "> $monthName[$n]\n";
  }
  echo "</select>";

  /* build selection list for the year */
  $startYr = date("Y", $today);         //get the year from $today
  echo "<select name='dateYr' >\n";
  for ($n=$startYr;$n<=$startYr+5;$n++)
  {
    echo " <option value=$n";
    if ($startYr == $n )
    {
      echo " selected";
    }
    echo "> $n\n";
  }
  echo "</select>\n";
?></td>
    <td width="13%">Name:</td>
    <td width="32%"><input name="cust_name" type="text" id="cust_name" size="40" /></td>
  </tr>
  <tr>
    <td>Type of Vehicle Req:</td>
    <td> <select name="typeOfvehicle" >
        <option value="4Seater">4 Seater Vehicle</option>
        <option value="7Seater">7 Seater Vehicle</option>
      </select></td>
    <td>Time</td>
    <td> Hr <select  name="timeHr"  > 
    <option>00</option>
    <option>01</option>
    <option>02</option>
    <option>03</option>
    <option>04</option>
    <option>05</option>
    <option>06</option>
    <option>07</option>
    <option>08</option>
    <option>09</option>
    <option>10</option>
    <option>11</option>
    <option>12</option>
    <option>13</option>
    <option>14</option>
    <option>15</option>
    <option>16</option>
    <option>17</option>
    <option>18</option>
    <option>19</option>
    <option>20</option>
    <option>21</option>
    <option>22</option>
    <option>23</option>
  </select>
Min <select name="timeMin" >
    <option>00</option>
    <option>05</option>
    <option>10</option>
    <option>15</option>
    <option>20</option>
    <option>25</option>
    <option>30</option>
    <option>35</option>
    <option>40</option>
    <option>45</option>
    <option>50</option>
    <option>55</option>
  </select></td>
    <td>Pick up Address:</td>
    <td><input name="cust_address" type="text" id="cust_address" size="50" /></td>
  </tr>
  <tr>
    <td>No. of Vehicles Req:</td>
    <td><select name="noOfVehicle" >
        <option value="1" selected="selected">1</option>
        <option value="2">2</option>
        <option value="3">3</option>
        <option value="4">4</option>
        <option value="5">5</option>
        <option value="6">6</option>
        <option value="7">7</option>
        <option value="8">8</option>
        <option value="9">9</option>
        <option value="10">10</option>
      </select></td>
    <td> </td>
    <td> </td>
    <td>Post Code:</td>
    <td><input name="cust_postCode" type="text" id="cust_postCode" size="15" /></td>
  </tr>
  <tr>
    <td>Booking Regularity:</td>
    <td><select name="regul" >
        <option value="0" selected="selected">Select</option>
	<option value="once">Once</option>
        <option value="daily">Daily</option>
        <option value="weekly">Weekly</option>
        <option value="monthly">Monthly</option>
      </select></td>
    <td> </td>
    <td> </td>
    <td>Tel / Mobile:</td>
    <td><input name="cust_tel" type="text" id="cust_tel" size="20" /></td>
    <tr>
    <td> </td>
    <td> </td>
    <td> </td>
    <td> </td>
    <td>Destination:</td>
    <td><input name="des" type="text" id="des" size="50" /></td>
</tr>
</table>

<hr>
  <tr>
  <td>
       <input name="submit" type="submit" value="Submit" />
  </td>
  <td>
        <input name="reset" type="reset" value="Reset" />
   </td><a href="blockBook.php">Make another Booking</a></form>
</tr></div>
<?
}
  [/code

zed420 · October 19, 2008

Any suggestions anyone???????? ???

Thanks

Zed

dropfaith · October 19, 2008

there doesnt seem to be any insert on the code you gave us? that is just a form.. we need the code that processes it

Maq · October 19, 2008

We need the actual SQL statements where you insert the data. If your tables are empty then obviously it's how you're inserting the data. Please post the code for that, thanks.

Sign In

Select query not working

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information