zed420 Posted October 17, 2008 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/ Share on other sites More sharing options...
rhodesa Posted October 17, 2008 Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-667967 Share on other sites More sharing options...
zed420 Posted October 17, 2008 Author Share Posted 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 ; Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-668168 Share on other sites More sharing options...
Maq Posted October 17, 2008 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-668172 Share on other sites More sharing options...
rhodesa Posted October 17, 2008 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-668186 Share on other sites More sharing options...
zed420 Posted October 17, 2008 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-668196 Share on other sites More sharing options...
rhodesa Posted October 17, 2008 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-668230 Share on other sites More sharing options...
Maq Posted October 17, 2008 Share Posted October 17, 2008 You're query is fine now, it's the part of your script where you add the "id" to the "blockBook". Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-668235 Share on other sites More sharing options...
zed420 Posted October 18, 2008 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-668792 Share on other sites More sharing options...
Maq Posted October 19, 2008 Share Posted October 19, 2008 How are you populating the table? Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-668901 Share on other sites More sharing options...
zed420 Posted October 19, 2008 Author Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-669052 Share on other sites More sharing options...
zed420 Posted October 19, 2008 Author Share Posted October 19, 2008 Any suggestions anyone???????? ??? Thanks Zed Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-669192 Share on other sites More sharing options...
dropfaith Posted October 19, 2008 Share Posted 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 Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-669198 Share on other sites More sharing options...
Maq Posted October 19, 2008 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/128837-select-query-not-working/#findComment-669298 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.