Solved

Add data to mysql without removing exsisting data

Posted on 2011-03-15
26
249 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
ID: 35138545
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
ID: 35138580
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
ID: 35138624
Some more reading:

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

Very nice "basic" PHP/MySQL tutorial.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:NeoAshura
ID: 35138767
@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
ID: 35138917
You will have to specify using which KEY you are trying to edit the record ...
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35139179
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
ID: 35139324
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
ID: 35139434
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
ID: 35139535
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
ID: 35139755
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
ID: 35139786
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
ID: 35139845
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
ID: 35139951
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
 
LVL 6

Author Comment

by:NeoAshura
ID: 35140001
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
ID: 35140031
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
ID: 35140033
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
ID: 35140057
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
ID: 35140150
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
ID: 35140161
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
ID: 35140162
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
ID: 35140222
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
ID: 35140918
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
ID: 35141469
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
ID: 35154883
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
ID: 35162583
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
ID: 35165200
Ahh Yeah that did it thanks.

:)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql database, schema and table creation 13 57
I think I know what this error means, but I need some other eyes on it... 32 49
Split wordpress loop 35 56
parse url to form? 7 25
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …

839 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