Solved

Add data to mysql without removing exsisting data

Posted on 2011-03-15
26
239 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:NeoAshura
  • 12
  • 12
  • 2
26 Comments
 
LVL 17

Expert Comment

by:psimation
Comment Utility
Yes, it is possible and it's all about how you create your tables and how you enter data into them.

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.

0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
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.
0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
Some more reading:

http://www.webmonkey.com/2010/02/PHP_and_MySQL_Tutorial_-_Lesson_2/

Very nice "basic" PHP/MySQL tutorial.
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
@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??
0
 
LVL 3

Expert Comment

by:pius_babbun
Comment Utility
You will have to specify using which KEY you are trying to edit the record ...
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
i tried the following but to no avail...

$query = "INSERT INTO `notes` (customer_name, notes) VALUES (".$val.",".$notes.")";

any ideas why it didnt work?

0
 
LVL 3

Expert Comment

by:pius_babbun
Comment Utility
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."')";
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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\mobilenum.php on line 127

line 127 is : $last_spoke_to=mysql_result($result,$i,"last_spoke_to");

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">&nbsp;</div>
				<div class="titleimage">
                	<div align="center"><img src="/test.gif" alt="navgif">
              	  </div>
				</div>
				<div class="bar">&nbsp;</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">&nbsp;</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>&nbsp;</th> 
  <td>&nbsp;</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>

Open in new window

0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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 . "'";
0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
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.

0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
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;
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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?
0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
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?

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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.
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
Ive also tried the following.

$sql = "SELECT customer.customer_name, customer.account_number, customer.contact_name, customer.telephone_number, customer.fax, customer.contact_mobile, customer.full_address, customer.network, notes.notes, notes.last_spoke_to FROM customer, notes WHERE customer_name = '" . $val . "'";

but i sitll get the same ambiguous error
0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
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)
 
0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
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.
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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??

                                                                                   
0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
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"
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
the query MUST contain my variable passed in the URL. that is a must. i appricate your  help here
0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
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"...
0
 
LVL 17

Expert Comment

by:psimation
Comment Utility
here is another reference site that seems to be very clear and concise:
http://www.sqltraining.org/selecting+data+from+multiple+tables/
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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.
<?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;
}
?>

Open in new window

picture.jpg
picture2.jpg
0
 
LVL 17

Accepted Solution

by:
psimation earned 500 total points
Comment Utility
You would want to open the TABLE (the HTML bit) BEFORE the while starts, and then close it after the while stops., This way, your while loop will only add <tr> and <td>'s to the same table.

ie,

<table>
while() {
echo "<tr><td>...";
}
</table>

However, if the only "incremental" data is the notes, then you will want to display the entire static section of the result - ie, the name, address, contact number etc, outside the loop, and ONLY the notes part you want to generate in a loop.

This may require 2 queries , ie,

1. you query the 1st table to get the stored "static" details that matches the variable you pass to it.
With this query, you create the "upper" part of the table to display since it will only return one row in your result set. You would also not need or want to use a while() statement here.

2. you then use the "link identifier" - or the column value that appears in both tables to query the second table. This will then most likely return more than one row, and for this part, you use a while loop to dynamically grow the <tr><td>...

3. after this while you close the loop.


Some pseudo code to illustrate:

$sql1 = select * from first table where name = $val;
$result = mysql_query($sql1);
$myrow = mysql_fetch_array($result);

echo "<table><tr><td>Column Heading</td><td>Another column</td></tr>";
$linkid = $myrow['ID']; //this is now the column that appears in both tables and holds the same value in both tables for records that are from the same customer.
$name = $myrow["name"];
$number = $myrow["number'];
echo "<tr><td>$name</td><td>$number</td>";
// this part thus creates the table, the first row of the table with headings, and the row containing the customer's details.

//now query the second table to get all the notes for this customer

$sql2 = "select * from table2 where id = $linkid"; //$linkid is from the first query and ensures that we now get only the rows from the notes table that matches the original customer ID.

$result2 = mysql_query($sql2); // this result will now probably contain more than one row.

//lets continue building the table

while ($myrow2 = mysql_fetch_array($result2){
echo "<tr><td>Note: </td><td>$myrow2["note"]</td>";
}
//now we close the table
echo "</table>";


HTH?



 
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
Ahh Yeah that did it thanks.

:)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now