tariq2009 Posted September 11, 2010 Share Posted September 11, 2010 [/code] Hi //open the connection $conn = mysql_connect("localhost", "techhom1_test", "pro176"); //pick the database to use mysql_select_db("testDB", $conn); //create the sql statement $sql = "INSERT INTO master_name values ('', '$firstname')"; //execute if (mysql_query($sql,$conn)){ echo "Record Added!"; } else{ echo "Somethin went wrong"; } the code above is insert code and working with out any errors but when i am submitting text into data using method below it is sending echo back Somethin went wrong.I think i am confused with the write name of the user table database is ***_test my tables are duty_number master_name in this _test user i am trying to add first name in master_name field so here is the submit code <FORM ACTION=insert.php METHOD=POST> <P>text to add:<br> <input type=text name=master_name size=30> <p><input type=submit name=submit value=Insert Record></p> </FORM> My table below in Data base [code]Field Type Null Default Comments name_id smallint(5) Yes NULL name_dateaddedn datetime Yes NULL name_datemodified datetime Yes NULL firstname varchar(75) Yes NULL lastname varchar(75) Yes NULL can someone please look at the code and find the error for me Thanks Quote Link to comment Share on other sites More sharing options...
fortnox007 Posted September 11, 2010 Share Posted September 11, 2010 hello Tariq, first of all i would recommend you to read a small tutorial about the INSERT in SQL, here is a nice one: http://en.w3schools.com/sql/sql_insert.asp Now back to your stuff. Your method relies on the exact order of your column names in the database. to give an example: INSERT INTO table_name VALUES (value1, value2, value3,...) this query will insert in table_name the values starting with column 1. This isn't very precise and leaves room for error, for instance if the field requires an integer and your trying to put a string in it. A better way is the following: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) Although this might result in the same, it's much more precise. And above all you can skip certain columns. So if you only want to put stuff in column 2 because column 1 for instance is an auto_incrementing primary key (user_ID's for example) you can do this while leaving that column out without error. INSERT INTO table_name (column2) VALUES (value2) Now if you have read this and look again at your query, what do you think happened. ones you found out dump that old error sensitive method and use the better one. Hope this helps i would love to hear so hehe Quote Link to comment Share on other sites More sharing options...
tariq2009 Posted September 13, 2010 Author Share Posted September 13, 2010 Thanks fortnox007 that helped a lot this what i did below Inserting into database below <head> <title></title> </head> <body> <form action="insert.php" method="post"> line: <input type="text" name="line" /> staff no: <input type="text" name="staffno" /> Firstname: <input type="text" name="firstname" /> Lastname: <input type="text" name="lastname" /> Sunday: <input type="text" name="Sunday" /> Monday: <input type="text" name="Monday" /> Tuseday: <input type="text" name="Tuseday" /> Wednesday: <input type="text" name="Wednesday" /> Thursday: <input type="text" name="Thursday" /> Friday: <input type="text" name="Friday" /> Saturday: <input type="text" name="Saturday" /> <input type="submit" /> </form> </body> </html> Insert code below <html> <head> <title></title> </head> <body> <?php $con = mysql_connect("localhost","******", "******"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("*****", $con); $sql="INSERT INTO Persons (line, staffno, firstname, lastname, Sunday, Monday, Tuseday, Wednesday, Thursday, Friday, Saturday) VALUES ('$_POST[line]','$_POST[staffno]','$_POST[firstname]','$_POST[lastname]','$_POST[sunday]','$_POST[Monday]','$_POST[Tuseday]','$_POST[Wednesday]','$_POST[Thursday]','$_POST[Friday]','$_POST[saturday]')"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "1 record added"; mysql_close($con) ?> </body> </html> Display data <html> <head> <title></title> </head> <body> <?php $con = mysql_connect("localhost","*****","*****"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("t*****1_test", $con); $result = mysql_query("SELECT * FROM Persons"); echo "<table border='1'> <tr> <th>Staff No</th> <th>Line</th> <th>Firstname</th> <th>Lastname</th> <th>Sunday</th> <th>Monday</th> <th>Tuseday</th> <th>Wednesday</th> <th>Thursday</th> <th>Friday</th> <th>Saturday</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['staffno'] . "</td>"; echo "<td>" . $row['line'] . "</td>"; echo "<td>" . $row['firstname'] . "</td>"; echo "<td>" . $row['lastname'] . "</td>"; echo "<td>" . $row['Sunday'] . "</td>"; echo "<td>" . $row['Monday'] . "</td>"; echo "<td>" . $row['Tuseday'] . "</td>"; echo "<td>" . $row['Wednesday'] . "</td>"; echo "<td>" . $row['Thursday'] . "</td>"; echo "<td>" . $row['Friday'] . "</td>"; echo "<td>" . $row['Saturday'] . "</td>"; echo "</tr>"; } echo "</table>"; mysql_close($con); ?> </body> </html> What i want is that all the week days to show allocated duties into it but how is the best way to create duties in database and stored with start time and finish time when i add the duty number it fetches the information in the day of the week i want to allocate it .Any tutorial or guide that will help me to do this task will be nice thanks . Quote Link to comment Share on other sites More sharing options...
fortnox007 Posted September 13, 2010 Share Posted September 13, 2010 Good to hear it helped you. As far as your new question. Your last sentence could you split that up in smaller bits and in order? This is hard to read And if you split it up its easier to figure out what functions/methods to use. Quote Link to comment Share on other sites More sharing options...
fortnox007 Posted September 13, 2010 Share Posted September 13, 2010 Is this maybe something to look it to also? http://www.yisongyue.com/schedulemaker/ I just googled php schedule. I have no idea what you really want and what the future intentions are of your app. But that's something to write down or as I always do draw (i am more visual interpreter ) Also it would be a good idea, but you will find that out after writing down what you want in a more precise manner. That spliiting data in the database would be nice. For instance a table named Users for user details. A table Tasks to add tasks dates and id's of first table. A table Notes, to make notes on Users or notes users made while calling customers. Things to google for are foreign Keys, JOIN (sql) and as extra you could watch video's of this dude to get the bigger picture: http://video.google.com/videoplay?docid=-3900628696112122500# Quote Link to comment Share on other sites More sharing options...
tariq2009 Posted September 13, 2010 Author Share Posted September 13, 2010 Hi i liked the Lightweight PHP Schedule Maker sorry i am going to be little annoying but how can use it with the MYSQL and insert my data by mysql in each day of the week i hope you understand what i mean thanks for your help Quote Link to comment Share on other sites More sharing options...
tariq2009 Posted September 13, 2010 Author Share Posted September 13, 2010 Hi can you please help me to add Sunday and Saturday and i want to add 30 lines for each day where i can fetch info from sql can you please help me with commands thanks Quote Link to comment Share on other sites More sharing options...
tariq2009 Posted September 13, 2010 Author Share Posted September 13, 2010 Hi can you please help me to add Sunday and Saturday and i want to add 30 lines for each day where i can fetch info from sql can you please help me with commands thanks Ok i have worked out how to add more days and lines now need lil help with stop adding information in time order i want it to add it by line order plus how to fetch sql to add staff number as the duties will stay same you can have look at it here http://proremotesupport.co.uk/rota/sampleschedule.php Quote Link to comment Share on other sites More sharing options...
tariq2009 Posted September 14, 2010 Author Share Posted September 14, 2010 can someone please help me with this error Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/techhom1/public_html/rota/update.php on line 28 and online 28 the code is $rows=mysql_fetch_array($result); <?php $host="localhost"; // Host name $username="**"; // Mysql username $password="**"; // Mysql password $db_name="****"; // Database name $tbl_name="Persons"; // Table name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); // get value of id that sent from address bar $id=$_GET['id']; // Retrieve data from database $sql="SELECT * FROM $tbl_name WHERE id='$id'"; $result=mysql_query($sql); $rows=mysql_fetch_array($result); ?> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <form name="form1" method="post" action="update_ac.php"> <td> <table width="100%" border="0" cellspacing="1" cellpadding="0"> <tr> <td> </td> <td colspan="3"><strong>Update data in mysql</strong> </td> </tr> <tr> <td align="center"> </td> <td align="center"> </td> <td align="center"> </td> <td align="center"> </td> </tr> <tr> <td align="center"> </td> <td align="center"><strong>firstname</strong></td> <td align="center"><strong>Lastname</strong></td> <td align="center"><strong>Staff No</strong></td> </tr> <tr> <td> </td> <td align="center"><input name="firstname" type="text" id="firstname" value="<? echo $rows['firstname']; ?>"></td> <td align="center"><input name="lastname" type="text" id="lastname" value="<? echo $rows['lastname']; ?>" size="15"></td> <td><input name="staffno" type="text" id="staffno" value="<? echo $rows['staffno']; ?>" size="15"></td> </tr> <tr> <td> </td> <td><input name="id" type="hidden" id="id" value="<? echo $rows['id']; ?>"></td> <td align="center"><input type="submit" name="Submit" value="Submit"></td> <td> </td> </tr> </table> </td> </form> </tr> </table> <? // close connection mysql_close(); ?> Quote Link to comment Share on other sites More sharing options...
fortnox007 Posted September 14, 2010 Share Posted September 14, 2010 HEy i was studying in the library so i didn't saw all your responses : ) give me a sec and I'll find it out -edit- Maybe place an echo right after you assign the value to $id via $_GET. // get value of id that sent from address bar $id=$_GET['id']; echo $id; See what value it echo's and if you expect it, because that should be the stuff to make the query work. -edit2- I also saw you use shorttags, don't <? // close connection mysql_close(); ?> Quote Link to comment Share on other sites More sharing options...
tariq2009 Posted September 15, 2010 Author Share Posted September 15, 2010 Hi its ok thanks for your reply and help now i have used echo $id;[/code] it is picking the id number and the errors is gone now when i submit the changes it is giving me new error which is UPDATE Persons SET firstname='', lastname='', staffno='', line='', Sunday='201', Monday='', Tuseday='', Wednesday='', Thursday='', Friday='', Saturday='', Duty='', starttime='', finishtime='', WHERE id=0ERROR <?php $host="localhost"; // Host name $username="techhom1_test"; // Mysql username $password="pro176"; // Mysql password $db_name="techhom1_test"; // Database name $tbl_name="Persons"; // Table name // Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $firstname=$_POST['firstname']; $lastname=$_POST['lastname']; $staffno=$_POST['staffno']; $line=$_POST['line']; $Sunday=$_POST['Sunday']; $Monday=$_POST['Monday']; $Tuseday=$_POST['Tuseday']; $Wednesday=$_POST['Wednesday']; $Thursday=$_POST['Thursday']; $Friday=$_POST['Friday']; $Saturday=$_POST['Saturday']; $Duty=$_POST['Duty']; $starttime=$_POST['starttime']; $finishtime=$_POST['finishtime']; $id=$_POST['id']; // update data in mysql database $id = intval($_POST['id']); $sql="UPDATE $tbl_name SET firstname='$firstname', lastname='$lastname', staffno='$staffno', line='$line', Sunday='$Sunday', Monday='$Monday', Tuseday='$Tuseday', Wednesday='$Wednesday', Thursday='$Thursday', Friday='$Friday', Saturday='$Saturday', Duty='$Duty', starttime='$strattime', finishtime='$finishtime', WHERE id=$id"; echo $sql; $result=mysql_query($sql); // if successfully updated. if($result){ echo "Successful"; echo "<BR>"; echo "<a href='list_records.php'>View result</a>"; } else { echo "ERROR"; } ?> the above code to update but it is not and giving id0=error and not updating the database i have tried almost every thing :'( please see if you can spot any error i am sorry to annoy you but i am starter and trying my best with your help Thanks Quote Link to comment Share on other sites More sharing options...
fortnox007 Posted September 15, 2010 Share Posted September 15, 2010 Heya, Well first of all Tuseday='$Tuseday' is wrong spelled, Unless your column name has that name which I doubt. Besides that, the way you are making this doesn't make sense. If you read back to the first reply I gave, you will notice that you are inserting empty values. which will if i am correct fail. Besides that it's better if you take this approach to have a 1 column for the day of the week. Because now you will always end up with 6 empty fields in a row which is inefficient. But even 1 column for a week of the day is a bit redundant. Better have a column with Just a formatted date. It's easier to maintain and it's less redundant. Read this: http://www.tizag.com/sqlTutorial/sqldate.php (so ones formatted right in your database, you will use a special select query and get the date back the way you want.) Hope this helps a bit. -edit- Oh I also noticed that your assigning the $id yourself. This is a possible problem. Mysql, has a nice solution for this. You make a column named ID (or something a like) Than you set it to Not NULL , primary key and auto increment. That way you dont have to do anything, and everytime someone adds a new row, the ID will increase with 1. -edit2- This might be a bit too much for now, But it's also a wise thing to separate stuff. Ideally you would like to end up with 2 tables: (table) USERS -userID -username -password -staffnumber (table) EVENTS -eventID -eventname -userID (so this one gets the value of the usertable) -date: -starttime -endtime -additional_info Quote Link to comment Share on other sites More sharing options...
fortnox007 Posted September 15, 2010 Share Posted September 15, 2010 Btw this is a nice and pleasant book to read. It has lots of cartoons and practise: http://headfirstlabs.com/books/hfsql/ Even if you read only the first 2 chapters you will have a better idea on why to separate info. (in your example. You might want to make a user but not yet an event. Or you might want to make 3 events for the same person but don't want to insert that users info 3 times) Ones you see specific info more than ones, you probably need to split things up. 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.