MySQL Help

[savagenoob]

OK, I have a client database (table name "clients")  that I need to insert multiple (unlimited) notes into with date and time stamps. I was thinking of the way to do this is to create a seperate table with a foreign key called "notes" but I don't know how to assign the notes entered to the specified client or link them together.

[ngreenwood6]

you would create 2 tables. first one with clients, second one with notes. in the one with clients you would have the client and the id. in the one with notes you would have a field with id, note, client id. Then when they enter a note link the table with the client id. here is an example

 

---clients---

id

client

---notes---

id

note

client_id

 

hopefully that makes sense to you.

[savagenoob]

Sweet, OK, that makes sense. Can I get an example query that would access a client and note simultaneously? I am building a view client page with an add/view notes section.

$result = mysql_query("SELECT Note FROM notes JOIN clients ON notes.Client_ID = clients.ID")

????

 

[ngreenwood6]

Yeah you need to use a join query in order to do it. however, i do not have alot of experience with join queries so maybe someone else can help him with this.

[savagenoob]

OK, my JOIN statement worked. Now I have to figure out how to add a note from the following page. Here is the code.

<?php

mysql_connect("localhost","root","xxxxxx"); 

//select which database you want to edit
mysql_select_db("xxxxxx"); 
   
$ID = $_GET['ID'];


	$result = mysql_query("SELECT * FROM clients WHERE ID=$ID ");

$fields_num = mysql_num_fields($result);
echo "<h1>Client: {$table}</h1>";
echo "<table border='1'><tr>";// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);    
echo "<td>{$field->name}</td>";
}
echo "</tr>\n";// printing table rows
while($row = mysql_fetch_row($result))
{    
echo "<tr>";    // $row is array... foreach( .. ) puts every element    // of $row to $cell variable    
foreach($row as $cell)        
echo "<td>$cell</td>";    
echo "</tr>\n";
}
mysql_free_result($result);
$result = mysql_query("SELECT Note FROM notes JOIN clients ON notes.Client_ID = clients.ID");

$fields_num = mysql_num_fields($result);
echo "<table border='1'><tr>";// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);    
echo "<td>{$field->name}</td>";
}
echo "</tr>\n";// printing table rows
while($row = mysql_fetch_row($result))
{    
echo "<tr>";    // $row is array... foreach( .. ) puts every element    // of $row to $cell variable    
foreach($row as $cell)        
echo "<td>$cell</td>";    
echo "</tr>\n";
}
mysql_free_result($result);

?>

</body>
</html>

 

This is taking an ID from a form on another page so I don't know how to do an UPDATE on the notes table using a text field form with the displayed clients ID. I tried adding this but failed.

 

<?
mysql_connect("localhost","root","xxxxxxx"); 

//select which database you want to edit
mysql_select_db("xxxxxxx"); 
    $ID = $_GET['ID'];
$Note =  mysql_escape_string($_POST['comments']);
$today = date("F j, Y");

$result = mysql_query("INSERT INTO notes (Note, date, Client_ID) VALUES ('$Note', '$today', '$ID')"); 


?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF']?>">
<textarea name="comments" cols="40" rows="5">
</textarea><br>
<input type="submit" value="Add Note" />
</form>

 

Anyone?

[savagenoob]

Ok, I decided to pass the ID of the client through a GET then assign it to a variable, but it still is not working. here is the code... I know for certain the GET is working, it is displaying the ID in the address field.

 

<?php
$ID = $_GET['ID'];
mysql_connect("localhost","root","xxxxxxxx"); 

//select which database you want to edit
mysql_select_db("xxxxxxx"); 

if(isset($_POST['save']))
{
    
$Note = $_POST['Notes'];
$today = date("F j, Y");

$result = mysql_query("INSERT INTO notes (Note, date, Client_ID) VALUES ('$Note', '$today', '$ID')");
echo "The note was successfully added."; 
}
?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF']?>">
<table width="700" border="0" cellpadding="2" cellspacing="1" align="center">
<td width="100">Note</td>
<td><textarea name="Notes" cols="50" rows="10"></textarea></td>
</tr>
<tr> 
<td width="100"> </td>
<td> </td>
</tr>
<tr> 
<td colspan="2" align="center"><input name="save" type="submit" value="Add Note"></td>
</tr>
</table>
</form>

 

Someone? Anyone?

[ngreenwood6]

When you say that it is not working you need to tell us what is not working about it. Is it giving you an error or not passing a variable. Also by looking at your code it looks like you would need register_globals turned on. It is suggested to not use register_globals so a way around this is to assign the $_POST['save'] value as $save and then to do the if statement with the variable.

[fenway]

When you say that it is not working you need to tell us what is not working about it.

Very true... and please limit your code snippets to only the error-causing portion

[savagenoob]

I know I am making this way more difficult than it has to be. I have it where it displays the client notes on seperate tables. I just want to add text area to add notes to the mysql table. The problem I am having is passing the ID to the mysql query since i have the notes table seperate from the clients table.

 

Sorry for the long snippets, I dont know where the problem is and there is not error coming up in the Apache error log. I know it has something to do with passing the variable. I didnt want to do the "add note" on a seperate page but it is the only way I can figure out how to pass the ID from the client table.

[ngreenwood6]

I think you are having a problem with the whitespacing in your query. try this:

 

$result = mysql_query("INSERT INTO notes (Note, date, Client_ID) VALUES ('$Note','$today','$ID')");

 

All i did was remove the spaces between the , and the ' in the VALUES section.

[savagenoob]

OK, I echo the mysql errors and got it to insert into the notes, BUT it still is not grabbing the ID variable and not inserting anything into Client_ID. Do I need to setup a foreign key? if so... how? \

[ngreenwood6]

You should not have to use a foreign key. when you post the note does the url still have the ?id= part at the end of the url. also Client_ID is case sensitive and you should not use a field with the name of date as that is a built in mysql function.

[fenway]

Then echo the query so we can see what it's actually doing.

[savagenoob]

Yeah, here is the addresss....

http://localhost/PHP-Login/addnote.php?ID=1

 

And I am totally a noob and don't know what you mean by echo'ing the query.... when i echo $result it display just the number "1". It inserts everything but the Client_ID or the GET variable $ID.

 

[ngreenwood6]

I think he wanted you to echo the query not the results

 

change this:

$result = mysql_query("INSERT INTO notes (Note, date, Client_ID) VALUES ('$Note', '$today', '$ID')");

 

to this:

$query = "INSERT INTO notes (Note, date, Client_ID) VALUES ('$Note','$today','$ID')";
$result = mysql_query($query);

 

then you can just do this:

 

echo $query;

[savagenoob]

Oh duh, idiot I am.... here it is...

 

INSERT INTO notes (Note, date, Client_ID) VALUES ('Deleted a vehicle','December 22, 2008','')

 

I will figure out that date part but that part is working for now. So I see the $ID variable is not passing into the query and I tried echo'ing $ID and nothing displayed so Im not sure the $ID = $_GET['ID']; is working. The id is still displaying on the address though so I don't know whats going on.

[ngreenwood6]

Yeah it is not getting the $id value. Everything is case sensitive so I would check to make sure that the url and the variable and the $_GET all are using the same case.

[savagenoob]

It is... its ID, even I cant screw that up. Somehow I have though lol

[ngreenwood6]

you could just try changing everything to client_id just to see if it works.