rockindano30 Posted May 12, 2008 Share Posted May 12, 2008 Well, I have a little problem here. I am working on a web app. the point of this database app is to keep track of employees and their keys assigned to them. It lists all employees and their keys they are assigned and what doors they open (departments). now i have three tables: employees(emp_id, Fname,Lname,email,dept_id) departments(dept_id,dept,phone) emp_keys(k_id,emp_id,key_num, accss) now i have a problem with displaying all records on employees table. there are around thirty employees and only displays half of that. everything else that i want it to do works just fine except that. here is code of where i display all records: <?php $emp_id=$_GET["emp_id"]; //get the mysql and store them in $result //change whatevertable to the mysql table you're using //change whatevercolumn to the column in the table you want to search $query = "SELECT employees.emp_id,Fname,Lname,email,dept,key_num,accss FROM emp_keys, departments, employees WHERE departments.dept_id=employees.dept_id and emp_keys.emp_id=employees.emp_id order by departments.dept"; $result = mysql_query($query); //grab all the content while($r=mysql_fetch_array($result)) { //the format is $variable = $r["nameofmysqlcolumn"]; //modify these to match your mysql table columns $dept=$r["dept"]; $asscc=$r["accss"]; $Fname=$r["Fname"]; $Lname=$r["Lname"]; $email=$r["email"]; $key_num=$r["key_num"]; $emp_id=$r["emp_id"]; ?> <table width="100%" border="0" cellpadding="1" cellspacing="0" class="table1"> <tr> <td width="14%"><font face="Arial, Helvetica, sans-serif"><?php print $dept; ?></font></td> <td width="17%"><font face="Arial, Helvetica, sans-serif" style="color:#CC6600"><?php print $Fname." ".$Lname; ?></font></td> <td width="28%"><font face="Arial, Helvetica, sans-serif"><?php print $email; ?></font></td> <td width="10%"><font face="Arial, Helvetica, sans-serif"><?php print $key_num; ?></font></td> <td width="50%"><font face="Arial, Helvetica, sans-serif"><?php print $asscc; ?></font></td> <td width="50%"> <?php print"<a href='delete.php?emp_id={$emp_id['emp_id']}'>Remove</a><br />";?> </td> </tr> </table> <?php }//end while loop ?> <?php } ?> Quote Link to comment Share on other sites More sharing options...
Skittalz Posted May 12, 2008 Share Posted May 12, 2008 shouldn't you put the html code into the while statement? Otherwise you will be redeclaring values in the arrays... I don't know if that was the problem you had Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 dont think so i'm guessing its dealing with the select $query statement. but i'll try it see how it goes thanks Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 no that didn't fix it. i have been on this problem for several hrs now and can't seem to find whats wrong with it. Quote Link to comment Share on other sites More sharing options...
Skittalz Posted May 12, 2008 Share Posted May 12, 2008 Can you please provide the source code for that whole file? Thanks Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 here it is <table width="100%" border="0" cellspacing="0" cellpadding="1"><form method="post" action="searchemps.php"> <tr> <td colspan="4"><font style="font-size:12px; color: #000000">Type in name of employee to search:</font></td> </tr> <tr> <td width="22%"><font style="font-size:12px; color:#000000">First Name: </font></td> <td width="29%"><input type="text" name="search" size=15 maxlength=25></td> <td width="21%"><font style="font-size:12px; color:#000000">Last Name:</font></td> <td width="28%"><input type="text" name="search2" size=15 maxlength=25></td> </tr> <tr> <td colspan="2"><input type="Submit" name="Submit" value=" " style="background:url(images/searchbutton.png) no-repeat center; height:35px; width:70px; border:0px; cursor:pointer" /> </form></td> <td colspan="2"><form method="post" action="allKeys.php"> <input type="submit" name="vall" value=" " style="background:url(images/viewallbutton.png) no-repeat center; height:35px; width:70px; border: 0px; cursor: pointer" /> </form> </td> </tr> </table> </div> <div class="p" style="clear:both"> <div align="center"> <?php if(!($db = @ mysql_connect('server', 'username', 'password'))) { echo 'Error: Could not connect to our database sorry for any inconvience.<br /> Please try at a later time.'; exit; } //select which database you want to edit mysql_select_db("db name); $vall=$_POST["vall"]; if(!isset($vall) || empty($vall)) { print"<p>No employees in Database."; } else{ $emp_id=$_GET["emp_id"]; //get the mysql and store them in $result //change whatevertable to the mysql table you're using //change whatevercolumn to the column in the table you want to search $query = "SELECT employees.emp_id,Fname,Lname,email,dept,key_num,accss FROM emp_keys, departments, employees WHERE departments.dept_id=employees.dept_id and emp_keys.emp_id=employees.emp_id"; $result = mysql_query($query); //grab all the content while($r=mysql_fetch_array($result)) { //the format is $variable = $r["nameofmysqlcolumn"]; //modify these to match your mysql table columns $dept=$r["dept"]; $asscc=$r["accss"]; $Fname=$r["Fname"]; $Lname=$r["Lname"]; $email=$r["email"]; $key_num=$r["key_num"]; $emp_id=$r["emp_id"]; ?> <table width="100%" border="0" cellpadding="1" cellspacing="0" class="table1"> <tr> <td width="14%"><font face="Arial, Helvetica, sans-serif"><?php print $dept; ?></font></td> <td width="17%"><font face="Arial, Helvetica, sans-serif" style="color:#CC6600"><?php print $Fname." ".$Lname; ?></font></td> <td width="28%"><font face="Arial, Helvetica, sans-serif"><?php print $email; ?></font></td> <td width="10%"><font face="Arial, Helvetica, sans-serif"><?php print $key_num; ?></font></td> <td width="50%"><font face="Arial, Helvetica, sans-serif"><?php print $asscc; ?></font></td> <td width="50%"> <?php print"<a href='delete.php?emp_id={$emp_id['emp_id']}'>Remove</a><br />";?> </td> </tr> </table> <?php }//end while loop ?> <?php } ?> this is the code Quote Link to comment Share on other sites More sharing options...
Skittalz Posted May 12, 2008 Share Posted May 12, 2008 Okay, thanks I am looking at it... whats the file name? Quote Link to comment Share on other sites More sharing options...
Skittalz Posted May 12, 2008 Share Posted May 12, 2008 Your connecting to the database as well correct Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 yes i'm connecting. as i mentioned it does everything works except that it only displays half my records Quote Link to comment Share on other sites More sharing options...
DarkWater Posted May 12, 2008 Share Posted May 12, 2008 Did you copy and paste that directly into the forum? Because if you did, you missed a quote here: mysql_select_db("db name); Should be: mysql_select_db("db name"); Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 this file is called allKeys.php oh yeah i forgot to type in that quote there my bad but thats not the problem. i have been breaking my head in trying to find why it only displays half my records Quote Link to comment Share on other sites More sharing options...
Skittalz Posted May 12, 2008 Share Posted May 12, 2008 Did you copy and paste that directly into the forum? Because if you did, you missed a quote here: mysql_select_db("db name); Should be: mysql_select_db("db name"); i hope hes not connecting to a db called 'db name' lol Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 lol no i'm not connecting to that, edit that part for the forum Quote Link to comment Share on other sites More sharing options...
DarkWater Posted May 12, 2008 Share Posted May 12, 2008 He erased it for privacy... o-o And change: $result = mysql_query($query); To: $result = mysql_query($query) OR die(mysql_error()); Secondly, please give me an example row from each table so I can see the relationship better. Quote Link to comment Share on other sites More sharing options...
revraz Posted May 12, 2008 Share Posted May 12, 2008 now i have a problem with displaying all records on employees table. there are around thirty employees and only displays half of that. everything else that i want it to do works just fine except that. here is code of where i display all records: $query = "SELECT employees.emp_id,Fname,Lname,email,dept,key_num,accss FROM emp_keys, departments, employees WHERE departments.dept_id=employees.dept_id and emp_keys.emp_id=employees.emp_id"; Just for you know, you are not displaying All records on the Employees table, you are displaying the ones that match your SELECT statement. Now granted if that statement does in fact match all of your records then they all should show, but chances are it's a problem in your query. Quote Link to comment Share on other sites More sharing options...
Skittalz Posted May 12, 2008 Share Posted May 12, 2008 If it displays half the records, try looking at which ones it is not displaying: is there a connection with the ones not displaying? Like is it not displaying the last 10 entries or something... From there we can isolate where the problem is occuring Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 k heres an example: Department | Employee name | email | key_num | access | remove ------------------------------------------------------------------------------------------- Public Roxy Gomez ventura@mail.com 08998 blank Remove Quote Link to comment Share on other sites More sharing options...
Skittalz Posted May 12, 2008 Share Posted May 12, 2008 Try removing the WHERE departments.dept_id=employees.dept_id and emp_keys.emp_id=employees.emp_id If that doesn't display them all its a coding problem Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 k removing that displays all records (the whole table) thirty time Quote Link to comment Share on other sites More sharing options...
Skittalz Posted May 12, 2008 Share Posted May 12, 2008 delete the while statment? Quote Link to comment Share on other sites More sharing options...
DarkWater Posted May 12, 2008 Share Posted May 12, 2008 I meant example database rows with the proper labels. >_> And try running the query in the MySQL client and see what the output is. If it displays all of them, it's a coding problem, if it doesn't, it's an SQL problem. Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 it displays each employee thirty times and there are thirty employees. and if remove the while statement it doesn't display anything Quote Link to comment Share on other sites More sharing options...
Skittalz Posted May 12, 2008 Share Posted May 12, 2008 well instead of the while try $r = mysql_fetch_array($result); that should work Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 k i ran the query in mysql client and it only displays the same thing half the records Quote Link to comment Share on other sites More sharing options...
rockindano30 Posted May 12, 2008 Author Share Posted May 12, 2008 it has to be a sql syntax problem but i can seem to figure it out. Quote Link to comment 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.