Help with inserting record in mysql database

tariq2009 · 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

fortnox007 · 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

tariq2009 · 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 .

fortnox007 · 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.

fortnox007 · 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#

tariq2009 · 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

tariq2009 · 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

tariq2009 · 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

tariq2009 · 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();

?>

fortnox007 · 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();

?>

tariq2009 · 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

fortnox007 · 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

fortnox007 · 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.

Sign In

Help with inserting record in mysql database

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

Join the conversation

Important Information