cyberangel

thanks o3d for the quick reply "where dp.pid = perso.pid and center.pid = dp.pid order by dp.id asc" is what i meant by adding the 3rd table. this is what i tried but as soon as i do this, it only updates the 1st row the right way and removes all date from the rest of the rows and put them on blank will try it with the join method, thanks for the tip. hope it works

holla everybody so back again with another crazy question am working on a small database with 3 to 4 tables. need at least 3 tables. the problem is that if i work with two of them, it works perfectly, as soon as i add the 3rd one, and try to edit a record, it edits only the 1st row and not all the rows required the first table is called "perso" containing the personal data of the employees the second table is called "center" this contains the information of the starting and ending dates of an employee on a center. the third table is called "dp" it contains the workplan of the employee all the 3 tables have a common fiield "pid" which is identical now i want the form to display alle the records suppose in the department IT, the workers have their plan of starting their work on a certian center on a certian date to a certian date. usually 2 weeks to a month. they should therefor appear on the plan of the specified center. it is simple if i am using the dp and center tables, but i want the perso table too so that i can display the names according to the pid. and maybe some other tables containing information of vacation etc. but as i said, as soon as i add the 3rd table to the query, it breaks down. please check the code: // the form code <? require "db_credentials.php"; // here is the problem, in the following query, if i add "from dp, perso, center where dp.pid = center pid it wont work. but as long as it looks like below, it works find... $result=mysql_query("select * from dp, perso where dp.pid = perso.pid order by dp.id asc"); // Fetch record rows in $result by while loop and put them into $row. while($row=mysql_fetch_assoc($result)){ ?> // then the table with cells filled with such code: <? echo $row['FirstName'] . " " . $row['LastName']; ?> <select name="monday_<? echo $row['id']; ?>" id="select3"> <option>Select</option> <option style="background-color:#FF0000 ">FDLS</option> <option style="background-color:#FF0000 ">FDFD</option> //and so on till sunday again <? } // End while loop. ?> ********************************************* <? if($_POST['Submit']){ // Connect database. $host="mysql"; $db_user="name"; $db_password="password"; $database="my_db"; mysql_connect($host,$db_user,$db_password); mysql_select_db($database); $result=mysql_query("select id from dp order by id asc"); while($row=mysql_fetch_assoc($result)){ $mo=$_POST["monday_".$row[id]]; mysql_query("update dp set Mo='$mo'' where id='$row[id]'"); } echo "--- DB Updated---"; } ?> ================================ all help will be very much appriciated thanks in advance

hi everybody simply love this forum because i always get the question perfectly answered. i am here this time with a rather complicated question: i am creating a simple duty plan for different departments for exeample department #1 is "Tech" and department #2 is "Service" different employees working in these departments change their dutys and will are sent to another department or section or go on holidays. this is why i have at least 4 different mysql tables to store the data:- personel(storing the personal information of the workers) dutyplan(storing the week's working daysy of the workers) departments(storing the starting and ending dates of the department change) vacation(stores the start and ending dates of the vacations) in all the tables the common id is the pid which is issued unique to every worker. i am still able to work only with the 1st two tables. i can edit and create new plans for the weeks for employees working in the departments with the following php code(submitting only to edit the plan) if i add a date range for example Monday the 24th of January to Sunday the 30th of January for an employee working in the Tech department to work a few days in service department, it will show the employee in the week on the plan of both departments. if both department heads edit the plan without communicating to each other, and knowing on which date the worker goes out and in to the department, the 1st one will plan him for the whole week on his dutyplan and the second department head will overwrite this plan(if edited) or if creating a new plan(create the duty of the worker also one more time). i want to avoid this confusion and manual work. the same is with the vacation or sickness tables, if the employee has vacation, the program should check and return the selected value in the pulldown menu with the value stored in the vacation or sickness tables appropriate to the date in the plan table. ************************************************************************ form.php: <? require "config.php"; $result=mysql_query("select * from personel, dutyplan, department, vacation WHERE personel.pid = dutyplan.pid and personel.pid = vacation.pid and dp.pid = departments.pid and departments.Section = 'Service' order by dp.id asc"); ?> <? while($row=mysql_fetch_assoc($result)){ ?> // after that i create table, displaying all the days of the week from monday to sunday and use this code <tr> <td><? echo $row['FirstName'] . " " . $row['LastName']; ?>: </td> <td> <select name="monday_<? echo $row['id']; ?>" id="select1"> <option><? echo $row['Mo']; ?></option> <option>Duty</option> <option>Free</option> <option>Vacation</option> //the same way down to sunday, for every day the different options to be selected. it displays the records perfectly, as long as there is no change of department planed and the vacation must also be selected manually. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ update.php <? if($_POST['Submit']){ require "includes/config.php"; $result=mysql_query("select * from dutyplan, departments, vacation order by dutyplan.id asc"); while($row=mysql_fetch_assoc($result)){ $mo=$_POST["monday_".$row[id]]; $tu=$_POST["tuesday_".$row[id]]; $we=$_POST["wednesday_".$row[id]]; $th=$_POST["thursday_".$row[id]]; $fr=$_POST["friday_".$row[id]]; $sa=$_POST["saturday_".$row[id]]; $su=$_POST["sunday_".$row[id]]; mysql_query("update dp set Mo='$mo', Tu='$tu', We='$we', Th='$th', Fr='$fr', Sa='$sa', Su='$su' where id='$row[id]'"); } echo "Records updated"; } ?> **************************************************** how would you gueys solve this problem? many thanks in advance

perfect you guys are great

hi everybody, every time i asked a question here, i got quick and perfect answers. now i am back again with one i have a simple form which updates some fields in a mysql table. everything works fine, am just trying to convert numbers posted through the form into words on the update page and it is not working :'( for example: the form sends score to the update page, it is either 1, 2 or 3 all i want is that if the score on the form page was 1 then it should update the field in the table with the value "Low" if 2 then "Average" if 3 then "High" the reason is that i dont want the user to select 1,2 or 3 and also low, average and high. so i can simply recieve 1,2 or 3 and update 2 fields at the same time. i tried it with elseif, it doesn't work my code is as follows: <? if($_POST['Submit']){ $host="mysql"; $db_user="me"; $db_password="mypassword"; $database="mydb"; mysql_connect($host,$db_user,$db_password); mysql_select_db($database); $result=mysql_query("SELECT id FROM mytable ORDER BY id ASC"); while($row=mysql_fetch_assoc($result)){ $name=$_POST["name_".$row[id]]; $lastname=$_POST["lastname_".$row[id]]; $email=$_POST["email_".$row[id]]; $score=$_POST["score_".$row[id]]; // this is where i don't simply want to update the record // with the $_POST, rather convert the number into aplhabets, i.e. 1=Low, 2=Average and 3=High mysql_query("UPDATE mytable SET name='$name', lastname='$lastname', email='$email', score='$score' where id='$row[id]'"); } echo "--- Update Complete ---"; } ?>

hi barand, thanks for the reply. i didn't write the code myself, i just found it in internet so i tried to add your code to the while loop somehow. I couldn't actually figure out how to add it. all i need is to print the room number and status of the room. for example, i want to see if the room number 110 is clean or dirty, i type 110 in the form text field in first page, it reads the csv file which includes the info and shows 110 - clean or 110 - dirty. i know i should learn more myself to get it done but will appreciate any help. Thanks

hi all, am back again with a problem, and i hope i will get wonderfull help here once again. what i am actually trying to do is that i have a .csv file wich is supposed to contain information for different room numbers. for example a list of clean and dirty rooms. i wanted to get a php script which can find the room in the list and display only that row. i found a script which works and displays all the rooms list, which is not what i need. the script is as follows: <?php $row = 1; $handle = fopen ("test.csv","r"); while ($data = fgetcsv ($handle, 1000, ",")) { $num = count ($data); print "<p> $num fields in line $row: <br>\n"; $row++; for ($c=0; $c < $num; $c++) { print $data[$c] . "<br>\n"; } } fclose ($handle); ?> it actually displays the complete csv file. i am using certian php scripts with mysql, where i have a form page with just 1 or 2 text boxes, it posts the variables to the second page which finds the data in mysql database and displays the result. i want the same to happen with csv file. can anybody please help me? many thanks in advance

Thanks Lovesmith, I am sure that your code is what I need, the only question is that how do I adjust my code(the code that I am using) into your code. I am totally new to MySQL and PHP. I know that it is a stupid question but I tried my best and checked it every way I could. Thanks again and any further help will be greatly appriciated.

Hi Everybody, being helped every time in this forum, I am sure that I will get good help this time as well. I have a page that should display certian records from MySQL database. The code that I use for it is: <?php // Connect to the database server $dbcnx = @mysql_connect("mysql", "my-user-name", "my-password"); if (!$dbcnx) { echo( "<P>Unable to connect to the " . "database server at this time.</P>" ); exit(); } // Connect to my-database if (! @mysql_select_db("my-database") ) { echo( "<P>Unable to locate the URL " . "database at this time.</P>" ); exit(); } ?> <?php // Request the text of all the URLs $result = mysql_query( "SELECT UID, FirstName, LastName, EmailAddr, Date, Comments FROM my-table ORDER BY UID DESC LIMIT 0 , 30"); if (!$result) { echo("<P>Error performing query: " . mysql_error() . "</P>"); exit(); } echo "<table border='0'>"; // Create header row echo "<tr>"; echo "<td bgcolor=#577095><strong><font color=#FFFFFF face=Courier size=2>Details</font></strong></td>"; echo "</tr>"; while ( $row = mysql_fetch_array($result) ) { echo "<tr>"; echo "<td bgcolor=#F4FFFD>" . "<font color=#0000FF face=Courier size=2>" . "Date: " . "</font>" . "<font face=Courier size=2>" . $row["Date"] . "</font>" . "<br>" . "<font color=#0000FF face=Courier size=2>" . "First Name: " . "</font>" . "<font face=Courier size=2>" . $row["FirstName"] . "</font>" . "<br>" . "<font color=#0000FF face=Courier size=2>" . "Last Name: " . "</font>" . "<font face=Courier size=2>" . $row["LastName"] . "</font>" . "<br>" . "<font color=#0000FF face=Courier size=2>" . "Comments: " . "</font>" . "<font face=Courier size=2>" . $row["Comments"] . "</font>" . "<br>" . "</td>"; echo "</tr>"; } echo "</table>\n"; ?> This works fine, the only problem is that I want to put a Dynamic Link to the next-> and <-previouse page on every page. Because the records are more then 30, and if I remove the LIMIT 0, 30, it becomes a very long page. Can somebody help me on that? Many Thanks in advance

Thank You Very Very Much toplay. You solved the whole problem

Thanks again toplay, you had been very helpfull and I know my question and code was pretty much confusing. Your third code with : SUM(IF(column1.............. is exactly what I needed, it shows the exact results. If you could help me modify it so that it displays the sum of (col1_total, col2_total and col3_total) instead of displaying the count of all the three columns in three cells, it would be great. For example now it shows: col1_total col2_total col3_total 40 30 10 It would be great if it would say for example: Total 80 Many Thanks in advance

Hi toplay, I tried to group it by all 3 columns but still displays the same result. I removed the EndDate totally and wrote it this way: SELECT column1,column2,column3, COUNT(*) AS total FROM mytable WHERE StartDate '2006-12-01' AND '2007-12-31' AND column1 = 'Very Good' AND column2 = 'Very Good' AND column3 = 'Very Good' GROUP BY column1 GROUP BY MtbrFrdlt, MtbrKmpt, RezptZmrResrv Is it any other way that I can get the result that I want. I mean it will also be Ok if I could calculate and display the result of multiple queries on the website with PHP or Javascript. I tried many things in PHP too, but couldn't do it. I don't know how to declare a variable that can be reconised all the way down in PHP. Suppose I count the column1 in the 1st query as "total1" and column2 as "total2" and 3 as "total3" and echo the result of "total1+total2+total3".

Hi everbody, I posted a question a few days ago, as I wanted to count the records consisting "Very Good" in 3 columns. thanks for the prompt advice. The problem is that the query works strange, it was supposed to count the record "Very Good" in all columns together. What it does right now is that it shows the sum of all very low. The count of "Very Good" in just one column is 40, but if I try to count all 3 columns together, it shows me the sum only "8" records. Can anybody guide me further please? SELECT column1,column2,column3, COUNT(*) AS total FROM mytable WHERE StartDate AND EndDate BETWEEN '2006-12-01' AND '2007-12-31' AND column1 = 'Very Good' AND column2 = 'Very Good' AND column3 = 'Very Good' GROUP BY column1 Thanks in advance

Hi drranch, I don't really know if the code used for Radio Buttons would be of any help as they are working properly. They insert the values into the table. What I now want to do is to read(Count) from the table. Anyway below is the code used for the Radio Buttons. _________________________________________ <INPUT TYPE=radio NAME="column1" Value="Very Good"> <INPUT TYPE=radio NAME="column1" Value="Good"> <INPUT TYPE=radio NAME="column1" Value="Bad"> <INPUT TYPE=radio NAME="column2" Value="Very Good"> <INPUT TYPE=radio NAME="column2" Value="Good"> <INPUT TYPE=radio NAME="column2" Value="Bad"> <INPUT TYPE=radio NAME="column3" Value="Very Good"> <INPUT TYPE=radio NAME="column3" Value="Good"> <INPUT TYPE=radio NAME="column3" Value="Bad"> <INPUT TYPE=SUBMIT NAME="submit" VALUE="SUBMIT"></FORM> ____________________________________________ Now these 9 Radio Buttons will insert the 3 Values in 3 different columns. All I want to do is for example to count how many times the value "Very Good" is inserted in all 3 columns. I can count it from 1 column simply with a COUNT statement in my previouse post. I just want to find a way to count it from all three columns at the same time.

Hi Everybody, I am new to MySQL and PHP and am experimenting a bit. Now I got a table which is updated by a form with radio buttons. The checked values of the Radio Buttons are "Very Good, Good and Bad". The problem that I am currently having is that The Radio Buttons update multilple columns, say Column1, Column2 and Column3. Means that all the three columns contain only these 3 values repeatedly. I to make reports I can count the Value "Very Good" in a columns using the following query: ------------------------------------------------ "SELECT column1, COUNT(*) AS total FROM backup WHERE StartDate AND EndDate BETWEEN '2006-12-01' AND '2007-12-31' AND column1 = 'Very Good' GROUP BY column1"); ------------------------------------------------ Now I want to count the value "Very Good" in all 3 columns and been Googling since hours without any success. Would appreciate all the help. The second query that I use to count all the group and count the records in a column is: ------------------------------------------------- SELECT column1, COUNT(*) AS total FROM backup WHERE StartDate AND EndDate BETWEEN '2006-12-01' AND '2007-12-31' GROUP BY column1 ORDER BY total DESC ---------------------------------------------------- Now I want to group and count the records in all the 3 columns together but here too I couldn't figure out. Please Help