Add data to mysql without removing exsisting data
Hi experts,
MySQL is not my forte, so i dont even know if this is possible.
I have some data already stored in a mysql database table called "notes" and i want to update the notes but keep the current notes as well...
so if the database already contained "Spoke to Phil"
and the user eneterd " spoke to julie" and then hit update the new box would then read as follows.
Spoke to Phil
Spoke to julie.
So both entries are present.
Is this even possible??
thanks again.
MySQL is not my forte, so i dont even know if this is possible.
I have some data already stored in a mysql database table called "notes" and i want to update the notes but keep the current notes as well...
so if the database already contained "Spoke to Phil"
and the user eneterd " spoke to julie" and then hit update the new box would then read as follows.
Spoke to Phil
Spoke to julie.
So both entries are present.
Is this even possible??
thanks again.
PS, if you need to elaborate, and use the same database for many more "conversations" for instance, then you need to start looking at relational tables and "linking" the tables with ID's so that you can call specific rows from the relational tables pertaining to only those you want to display.
I suggest you google "relational db's" for some more reading on that subject and "normalization" as well.
I suggest you google "relational db's" for some more reading on that subject and "normalization" as well.
Some more reading:
http://www.webmonkey.com/2010/02/PHP_and_MySQL_Tutorial_-_Lesson_2/
Very nice "basic" PHP/MySQL tutorial.
http://www.webmonkey.com/2010/02/PHP_and_MySQL_Tutorial_-_Lesson_2/
Very nice "basic" PHP/MySQL tutorial.
ASKER
@psimation: thanks for your reply..
so what you are surgestting is i create a table called "notes" and relate this to the customer by for example account number?
if all i want is the notes column and the account number would this be sturctured as
ID Account number Notes
1 123456 blah blah
2 123455 test test
etc??
so what you are surgestting is i create a table called "notes" and relate this to the customer by for example account number?
if all i want is the notes column and the account number would this be sturctured as
ID Account number Notes
1 123456 blah blah
2 123455 test test
etc??
You will have to specify using which KEY you are trying to edit the record ...
ASKER
i tried the following but to no avail...
$query = "INSERT INTO `notes` (customer_name, notes) VALUES (".$val.",".$notes.")";
any ideas why it didnt work?
$query = "INSERT INTO `notes` (customer_name, notes) VALUES (".$val.",".$notes.")";
any ideas why it didnt work?
Update the code as below and check ..you need add the single quotes for the values inside the query
$query = "INSERT INTO `notes` (customer_name, notes) VALUES (".$val.",".$notes.")";
$query = "INSERT INTO `notes` (customer_name, notes) VALUES ('".$val."','".$notes."')"
$query = "INSERT INTO `notes` (customer_name, notes) VALUES (".$val.",".$notes.")";
$query = "INSERT INTO `notes` (customer_name, notes) VALUES ('".$val."','".$notes."')"
ASKER
still wont work i get the following error:
Warning: mysql_result() [function.mysql-result]: last_spoke_to not found in MySQL result index 7 in I:\EasyPHP-5.3.5.0\www\mob
line 127 is : $last_spoke_to=mysql_resul
any ideas? code for page is attached.
Warning: mysql_result() [function.mysql-result]: last_spoke_to not found in MySQL result index 7 in I:\EasyPHP-5.3.5.0\www\mob
line 127 is : $last_spoke_to=mysql_resul
any ideas? code for page is attached.
<?php
session_start();
ob_start();
?>
<?
error_reporting(E_ALL);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Talk Business Solutions</title>
<link id="css_default" href="unity_scm.css"
rel="stylesheet" type="text/css">
</head><body>
<div class="fullpage">
<div class="topbar">
<div class="topbar_left"></div>
</div>
<div class="secondbar">
</div>
<div class="header">
<div align="center"><span class="secondbar"><img src="index_files/unity_scm_images/header.gif" width="970" height="140" alt="headerimage" /></span></div>
</div>
<div class="leftcol">
<div class="bar"> </div>
<div class="titleimage">
<div align="center"><img src="/test.gif" alt="navgif">
</div>
</div>
<div class="bar"> </div>
<div align="center"><a name="sitenav"></a>
<ul class="navbar">
<li><a
href="logout.php" accesskey="1">Logout</a></li>
<li><a href="link2.php" accesskey="2">Link2</a></li>
<li><a href="link3.php" accesskey="3">Link3</a></li>
<li><a href="link4.php" accesskey="4">Link4</a></li>
<li><a href="link5.php" accesskey="5">Link5</a></li>
<li><a href="link6.php" accesskey="6">Link6</a></li>
<li><a href="link7.php" accesskey="7">Link7</a></li>
</ul>
</ul>
</div>
<div class="sidebarbottom"> </div>
</div>
<div class="mainarea">
<div align="center">
</div>
<div class="pagearea">
<div align="center"><a name="content"></a>
</div>
<h2 align="center">Talk Business Solutions</h2>
<p align="center" style="font-size: 0.8em; border-color: rgb(221, 221, 221);"><em>Here is a list of all customers in relation to the company selected..</em></p>
<h3 align="center" class="banner">Customers</h3>
<p align="center"></p>
<title></title>
<div align="center">
<body>
<body bgcolor="#c9cfef" link="#000000" vlink="#000000">
</div>
<tr>
</div>
<td width="100" valign="top"></td>
<td width="100" nowrap><div align="center"><br />
</div></td>
<p align="center">
<?php
error_reporting(E_ALL);
include 'myphp.php';
$val = $_GET['val'];
$sql = "SELECT * FROM customer WHERE customer_name = '" . $val . "'";
$result = mysql_query($sql) or die(mysql_error() . " IN $sql");
$rs = mysql_query($sql);
while($row = mysql_fetch_array($rs))
{
echo"<table>";
echo "<tr><td>Company:</td><td>".$row['customer_name']."</td></tr>";
echo "<tr><td>Account Number:</td><td><a href=\"account.php?val=".$row['account_number']."\">".$row['account_number']."</a></td></tr>";
echo "<tr><td>Contact Name:</td><td>".$row['contact_name']."</td></tr>";
echo "<tr><td>Telephone Number:</td><td>".$row['telephone_number']."</td></tr>";
echo "<tr><td>Fax:</td><td>".$row['fax']."</td></tr>";
echo "<tr><td>Contact Mob Number:</td><td>".$row['contact_mobile']."</td></tr>";
echo "<tr><td>Address:</td><td>".$row['full_address']."</td></tr>";
echo "<tr><td>Network:</td><td>".$row['network']."</td></tr>";
echo "<tr><td>Last Spoke To:</td><td>
<div style=\"width: 190px;height: 250px;overflow:auto;\">"
.$row['last_spoke_to']."</div></td></tr>";
echo "<tr><td>Notes:</td><td>
<div style=\"width: 190px;height: 250px;overflow:auto;\">"
.$row['notes']."</div></td></tr>";
echo "<tr><td>Kit Fund Level:</td><td>".$row['kit_fund_level']."</td></tr>";
echo"</table>";
}
?>
<?php
$query=" SELECT * FROM `notes` WHERE customer_name = '" . $val . "'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) {
$last_spoke_to=mysql_result($result,$i,"last_spoke_to");
$notes=mysql_result($result,$i,"notes");
++$i;
}
?>
<title></title>
<body>
<form name="register" method="post" action="edituploadednotes.php">
<input type="hidden" value="<?php echo $_GET['val']; ?>" name="val" >
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td colspan="14" bgcolor="#ffffff"><strong>Edit Notes</strong></td>
</tr>
<tr>
<td width="249" bgcolor="#ffffff">Last spoke to</td>
<td width="7" bgcolor="#ffffff">:</td>
<td width="393" bgcolor="#ffffff"><textarea name="last_spoke_to" cols="40" rows="10">
Enter your last spoke to here....
</textarea>
<br /></td>
</tr>
<tr>
<td width="249" bgcolor="#ffffff">Notes</td>
<td width="7" bgcolor="#ffffff">:</td>
<td width="393" bgcolor="#ffffff"><textarea name="notes" cols="40" rows="10">
Enter your notes here....
</textarea>
<br /></td>
</tr>
<td wdith="1298" bgcolor="#ffffff">
<input type="submit" name="Submit" value="Update" /></td>
</tr>
</table></td>
</form>
</p>
<th> </th>
<td> </td>
<div align="center"><br/>
<img src="" width="" height="
">
</div>
<div align="center">
</div>
</div>
</div>
<div class="spacer"></div>
<div class="footer">
<div class="secondbar_bottom">
<div align="center">
<?php //echo 'Welcome ' .''. $_SESSION['myusername'];?>
</div>
</div>
<div class="bottombar">
<div align="center">Talk Business Solutions</div>
</div>
</div>
<div align="center">
</div>
</div>
</body></html>ASKER
ok i took out the other select and deleted a few columns now i get the following error.
Column 'customer_name' in where clause is ambiguous IN SELECT * FROM customer, notes WHERE customer_name = 'Mrs Anna Norris'
i know this means ive got the customer name in both tables but i want it in both tables to identify who's is who's notes. here is the select statement. how could i modify this to get the output from the tables
$sql = "SELECT * FROM customer, notes WHERE customer_name = '" . $val . "'";
Column 'customer_name' in where clause is ambiguous IN SELECT * FROM customer, notes WHERE customer_name = 'Mrs Anna Norris'
i know this means ive got the customer name in both tables but i want it in both tables to identify who's is who's notes. here is the select statement. how could i modify this to get the output from the tables
$sql = "SELECT * FROM customer, notes WHERE customer_name = '" . $val . "'";
when you do an INSERT, the convention is as follow: (`column`,`column`) values ('$value1','$value2')
In your example, your columns are called ID, Account number, Notes:
a) keep your column names manageable, ie, no spaces.
b) make sure your INSERT statement matches your columns of your table.
In your example, your columns are called ID, Account number, Notes:
a) keep your column names manageable, ie, no spaces.
b) make sure your INSERT statement matches your columns of your table.
also, the select statement has the following convention:
select * from tablename
your attempt here:
$sql = "SELECT * FROM customer, notes WHERE customer_name = '" . $val . "'";
should be something like this:
select * from tablename where customer_name = "$val";
OR
select customer_name, notes from tablename where customer_name = $val;
select * from tablename
your attempt here:
$sql = "SELECT * FROM customer, notes WHERE customer_name = '" . $val . "'";
should be something like this:
select * from tablename where customer_name = "$val";
OR
select customer_name, notes from tablename where customer_name = $val;
ASKER
im selecting data from two tables,
im selecting data from the customer table
and im selecting data from the notes table.
for example customer_name, address, telephone number etc from the customer table.
and from the notes table, notes, and last_spoke_to based on the "customer_name" given in the variable.
how would that work?
im selecting data from the customer table
and im selecting data from the notes table.
for example customer_name, address, telephone number etc from the customer table.
and from the notes table, notes, and last_spoke_to based on the "customer_name" given in the variable.
how would that work?
select * from tablename returns all rows and all collumns in an array.
You can then use mysql_fecth_assoc() function as per my original example in first post to create an associative array of each row, and then combine that with a while() loop to step through each row.
the follwing example does the following:
1. $sql= "select * from tablename;
2. $result=mysql_query($sql);
3. while ($myrow = mysql_fetch_assoc($result)
4. //more detail to follow;
5. }
Line1 is your sql statement.
Line 2 "executes" your staement and the result is placed into the $result variable.
Line3 starts a while loop, and takes as it's argument the output of the assignment of the mysql_fetch_assoc() function.
This does a couple of things.
Like all PHP statement (like IF), while() too does a simple check to see if the parameter passed to it is more than 0.
So, the first time the while loop executes, it assigns an associative array of the first row in $result to $myrow. This means, that you can access each of the elements in this new array by either the array element number, OR the array element name.
IE, in your example, each row has 3 columns; ID, account_number and notes.BUT, ID is then also the "0" element, account_number is the "1" element and notes is the "2" element.
So, you can then access the value stored in each array element by either adressing it as:
$ID = $myrow[0]; OR, $ID = $myrow['ID'];
After each assignment of $myrow to the mysql_fecth_assoc() function, the internal pointer of the $result resultset is automatically advanced, and the while loop repeats. That is, untill it reaches the last row, and the result is 0, causing the while loop's argument to become 0 and thus terminating the loop and moving on the next line of code after the while loop's closing brackets.
So, on line 4 you can start displaying your results using above explanation of how to access the values contained in the associative array,
Line 5 closes the while loop.
HTH?
You can then use mysql_fecth_assoc() function as per my original example in first post to create an associative array of each row, and then combine that with a while() loop to step through each row.
the follwing example does the following:
1. $sql= "select * from tablename;
2. $result=mysql_query($sql);
3. while ($myrow = mysql_fetch_assoc($result)
4. //more detail to follow;
5. }
Line1 is your sql statement.
Line 2 "executes" your staement and the result is placed into the $result variable.
Line3 starts a while loop, and takes as it's argument the output of the assignment of the mysql_fetch_assoc() function.
This does a couple of things.
Like all PHP statement (like IF), while() too does a simple check to see if the parameter passed to it is more than 0.
So, the first time the while loop executes, it assigns an associative array of the first row in $result to $myrow. This means, that you can access each of the elements in this new array by either the array element number, OR the array element name.
IE, in your example, each row has 3 columns; ID, account_number and notes.BUT, ID is then also the "0" element, account_number is the "1" element and notes is the "2" element.
So, you can then access the value stored in each array element by either adressing it as:
$ID = $myrow[0]; OR, $ID = $myrow['ID'];
After each assignment of $myrow to the mysql_fecth_assoc() function, the internal pointer of the $result resultset is automatically advanced, and the while loop repeats. That is, untill it reaches the last row, and the result is 0, causing the while loop's argument to become 0 and thus terminating the loop and moving on the next line of code after the while loop's closing brackets.
So, on line 4 you can start displaying your results using above explanation of how to access the values contained in the associative array,
Line 5 closes the while loop.
HTH?
ASKER
im sorry but i dont see how this helps me if im trying to display data from TWO separate tables this may be me being stupid? i dont know.
ASKER
Ive also tried the following.
$sql = "SELECT customer.customer_name, customer.account_number, customer.contact_name, customer.telephone_number,
but i sitll get the same ambiguous error
$sql = "SELECT customer.customer_name, customer.account_number, customer.contact_name, customer.telephone_number,
but i sitll get the same ambiguous error
OK, sorry, if you have 2 tables, then your select statements needs to follow a different notation:
lets say you have:
table1 and table2
table1 has ID, Notes, and table2 has ID and name as collumns.
now, when you enter data into these tables, you should include another key in one of the tables that can act as the lookup key for the other table, so, add a column to table2 called ID_table1
Now, when you add data to table 1 and two, make sure that the alue of table1.ID is also stored in table2.ID_table1
Now, when you need to select data from these two tables, use the following simple notation:
select * from table1, table2 where table1.ID = table2.ID_table1
This, due to the * will select ALL the rows and all the columns from both tables and combine them together. ( BTW, this is the same as a JOIN"
Now, with the result of this query, you can continue as per my previous examples.
NB: the only caveat of these type of queries are that if your tables contain columns with the same name, you should use aliases in your select query, and not use select*
You could thus do something like:
select table1.field1 as tf, table2,field1 as tf2 from table1,table2 where tf=tf2
(note, you can do aliases for the table names as well)
lets say you have:
table1 and table2
table1 has ID, Notes, and table2 has ID and name as collumns.
now, when you enter data into these tables, you should include another key in one of the tables that can act as the lookup key for the other table, so, add a column to table2 called ID_table1
Now, when you add data to table 1 and two, make sure that the alue of table1.ID is also stored in table2.ID_table1
Now, when you need to select data from these two tables, use the following simple notation:
select * from table1, table2 where table1.ID = table2.ID_table1
This, due to the * will select ALL the rows and all the columns from both tables and combine them together. ( BTW, this is the same as a JOIN"
Now, with the result of this query, you can continue as per my previous examples.
NB: the only caveat of these type of queries are that if your tables contain columns with the same name, you should use aliases in your select query, and not use select*
You could thus do something like:
select table1.field1 as tf, table2,field1 as tf2 from table1,table2 where tf=tf2
(note, you can do aliases for the table names as well)
your ambiguity stems fro the fact that your where clause does not make the link between the two tables. my previous post should show you what I mean. You MUST have something like a "where value from table1 = value from table2" < this is pseudo code to explain the concept and not literal.
ASKER
i have an id in column 2 as shown below
table below called notes
ID customer_name notes last_spoke_to
1 Mrs Anna Norris 0
2 Mrs Anna Norris 0
3 Mrs Anna Norris test
4 Mrs Anna Norris Enter your notes here.... Enter your last spoke to here....
and here is the other table called customer
customer_name title first_name last_name account_number dealer staff full_address telephone_number contact_name fax contact_mobile network kit_fund_level
i need to get all data from customer and also notes and last_spoken_to from the notes table.. from your answer i understand this to be the following..
select * from customer, notes where customer.customer_name= notes.ID AND WHERE customer_name = '" . $val . "'";
is that correct??
table below called notes
ID customer_name notes last_spoke_to
1 Mrs Anna Norris 0
2 Mrs Anna Norris 0
3 Mrs Anna Norris test
4 Mrs Anna Norris Enter your notes here.... Enter your last spoke to here....
and here is the other table called customer
customer_name title first_name last_name account_number dealer staff full_address telephone_number contact_name fax contact_mobile network kit_fund_level
i need to get all data from customer and also notes and last_spoken_to from the notes table.. from your answer i understand this to be the following..
select * from customer, notes where customer.customer_name= notes.ID AND WHERE customer_name = '" . $val . "'";
is that correct??
PS, your "link" between the tables does not have to be your ONLY condition after the WHERE clause, you could for instance do somthing like this:
select t1.field1 as f1, t1.field2 as f2, t2.field1 as t2f1, t2.field2 as t2f2 from table1 as t1, table2 as t2 where t1.f1 = t2.t2f1 AND t1.f2="whatever"
select t1.field1 as f1, t1.field2 as f2, t2.field1 as t2f1, t2.field2 as t2f2 from table1 as t1, table2 as t2 where t1.f1 = t2.t2f1 AND t1.f2="whatever"
ASKER
the query MUST contain my variable passed in the URL. that is a must. i appricate your help here
OK, you can only compare apples with apples.
So, in your example above:
select * from customer, notes where customer.customer_name= notes.ID AND WHERE customer_name = '" . $val . "'";
notes.ID contains an integer value, and customer.customer_name contains a text value - they will NEVER equal.
With what you have there, your query should look like this:
select * from customer, notes where customer.customer_name = notes.customer_name AND customer.customer_name = "$val"; (I'm asuming the $val will be a customer's name you supply into your code.
Now, best practise would be to have another numeric ID value stores in notes that contains the same value as the ID column in your first table so tha you compare generated number values with each other to obtain the "link".
When you add names to DB's and you do not deal with the "funny" characters likethe ' in "O'brien", or worse even, you "deal" with it when adding it to your first table, but not when adding it to the notes table, your query will also never return a result because the value stored in the tables will differ by maybe even just a "space"...
So, in your example above:
select * from customer, notes where customer.customer_name= notes.ID AND WHERE customer_name = '" . $val . "'";
notes.ID contains an integer value, and customer.customer_name contains a text value - they will NEVER equal.
With what you have there, your query should look like this:
select * from customer, notes where customer.customer_name = notes.customer_name AND customer.customer_name = "$val"; (I'm asuming the $val will be a customer's name you supply into your code.
Now, best practise would be to have another numeric ID value stores in notes that contains the same value as the ID column in your first table so tha you compare generated number values with each other to obtain the "link".
When you add names to DB's and you do not deal with the "funny" characters likethe ' in "O'brien", or worse even, you "deal" with it when adding it to your first table, but not when adding it to the notes table, your query will also never return a result because the value stored in the tables will differ by maybe even just a "space"...
here is another reference site that seems to be very clear and concise:
http://www.sqltraining.org/selecting+data+from+multiple+tables/
http://www.sqltraining.org/selecting+data+from+multiple+tables/
ASKER
thanks for your help, i will try
select * from customer, notes where customer.customer_name = notes.customer_name AND customer.customer_name = "$val"; (I'm asuming the $val will be a customer's name you supply into your code.
When im back at work on thursday and let u know how i get on many thanks again you are very helpful
select * from customer, notes where customer.customer_name = notes.customer_name AND customer.customer_name = "$val"; (I'm asuming the $val will be a customer's name you supply into your code.
When im back at work on thursday and let u know how i get on many thanks again you are very helpful
ASKER
Hi, Your query worked apart from it echo'ed new tables with the data in rather than adding the data to the current table ive attached a screenshot to show you what i mean..
is there anyway just to get all the data to display in one box rather than constantly repeat itself??
thanks.
code is also supplied.
picture2.jpg
is there anyway just to get all the data to display in one box rather than constantly repeat itself??
thanks.
code is also supplied.
<?php
error_reporting(E_ALL);
include 'myphp.php';
$val = $_GET['val'];
$sql = "select * from customer, notes where customer.customer_name = notes.customer_name AND customer.customer_name = '" . $val . "'";
$result = mysql_query($sql) or die(mysql_error() . " IN $sql");
$rs = mysql_query($sql);
while($row = mysql_fetch_array($rs))
{
echo"<table>";
echo "<tr><td>Company:</td><td>".$row['customer_name']."</td></tr>";
echo "<tr><td>Account Number:</td><td><a href=\"account.php?val=".$row['account_number']."\">".$row['account_number']."</a></td></tr>";
echo "<tr><td>Contact Name:</td><td>".$row['contact_name']."</td></tr>";
echo "<tr><td>Telephone Number:</td><td>".$row['telephone_number']."</td></tr>";
echo "<tr><td>Fax:</td><td>".$row['fax']."</td></tr>";
echo "<tr><td>Contact Mob Number:</td><td>".$row['contact_mobile']."</td></tr>";
echo "<tr><td>Address:</td><td>".$row['full_address']."</td></tr>";
echo "<tr><td>Network:</td><td>".$row['network']."</td></tr>";
echo "<tr><td>Last Spoke To:</td><td>
<div style=\"width: 190px;height: 250px;overflow:auto;\">"
.$row['last_spoke_to']."</div></td></tr>";
echo "<tr><td>Notes:</td><td>
<div style=\"width: 190px;height: 250px;overflow:auto;\">"
.$row['notes']."</div></td></tr>";
echo "<tr><td>Kit Fund Level:</td><td>".$row['kit_fund_level']."</td></tr>";
echo"</table>";
}
?>
<?php
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$i=0;
while ($i < $num) {
$last_spoke_to=mysql_result($result,$i,"last_spoke_to");
$notes=mysql_result($result,$i,"notes");
++$i;
}
?>picture2.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ahh Yeah that did it thanks.
:)
:)
1. If you created a table called NOTES that contains an ID ( that is INT and auto increment), and a NOTES field of type TEXT (for example), then a simple 'INSERT INTO NOTES (`NOTES`) values ('This is the note'); then this will create a new row with ID = 1, and the "NOTES" field will contain the "This is the note" text.
If you use more INSERTS as per above example, it will create a new row with a new, unique and incremental ID each time you INSERT.
Now, when you READ from the table for display purposes, you will use "SELECT" statement, like so:
SELECT * FROM NOTES;
This will return ALL the rows in the table.
If this is what you need, then you will usually use PHP to make displaying this easier. Something like this:
$sql = "select * from NOTES";
$result = mysql_query($sql);
while ($myrow = mysql_fetch_assoc($result)
echo $myrow['NOTES']."<br>";
}
this snippet ( along with the rest of the required mysql functions such as mysql_connect and select_db) will then print out all the rows.
You can obviously expand this to be more specialized by using "WHERE" clauses in the select to specify a range of rows you are interested in.