[RESOLVED] Problem Updating MySQL.

[jc87]

Hi all,

Got a slight problem with my code which I have been messing around with all day and can't seem to get it working right.

Code available here; http://rafb.net/p/ptdHkl54.html

First, an explanation; On my site, I'm trying to make it so that a user can search for a particular food, and if one is returned, all the user the option of adding into the user table to record that they have eaten that food.

I am trying to make it so that a user can record a number of different foods that they have eaten but this is what is giving me problems.

This has involved me making a couple of columns, Food_Eaten1, Food_Eaten2... (I will look to add in more but want to get this working first) which record this.

The search should look to see if a value has been added into Food_Eaten1 and if it's empty put it in there, otherwise if Food_Eaten1 already contains a value, put it in Food_Eaten2.

At the moment I can add one food, but when I go to add a different food, the PHP/MYSQL is updating the Food_Eaten1 column, thus overwriting the previously stored food. Any help would be appreciated.

Thanks in advance.

[tomhath]

Echo what you have in $food_eaten1 and $food_eaten2 right after the SELECTs and right before the UPDATEs. It looks like they are getting undefined somewhere.

The SELECT queries return a resource, both of them should always be set even if the column is null.

[djjjozsi]

$food_eaten1 = mysql_query("SELECT Food_Eaten1 FROM jos_users WHERE id=".$user->id." || die(mysql_error())");

$food_eaten2 = mysql_query("SELECT Food_Eaten2 FROM jos_users WHERE id=".$user->id." || die(mysql_error())");

you inserted the die error handling in the SQL query. This is a problem.

[tomhath]

Yipes. I kept looking at that quote after $user->id and thinking it didn't belong there, but completely missed what followed. Good catch.

[jc87]

Thanks for your input.

Well spotted djjjozsi - must have been a typo on my end, however after changing the bracketing it still makes no difference. Which must indicate that the MySQL is running correctly?

tomhath - As suggested I've added in some echo statements

 $food_eaten1 = @mysql_query("SELECT Food_Eaten1 FROM jos_users WHERE id=".$user->id."") || die(mysql_error());

echo "Food Eaten 1 Select: ". $food_eaten1 .""; 

and the same for Food Eaten 2 after select, and this returns; Food Eaten 1 Select: 1 Food Eaten 2 Select: 1 before the search is conducted.

However, when adding this in after the update of Food_Eaten1;

$add1 = @mysql_query("UPDATE jos_users SET Food_Eaten1 = '".$_POST[foodname]."' WHERE id=".$user->id."") || die(mysql_error());

$db->setQuery($add1);
echo "Food Eaten 1 Update: ". $food_eaten1 .""; 

After performing a search and 'adding' the food, it returns; Food Eaten 1 Update: 1 Food Added Successfully.

Also added this below Food_Eaten2 update however, if I perform the search again and add another food, the same is displayed (Food Eaten 1 Update: 1 Food Added Successfully.) instead of Food Eaten 2 Update: 1.

For reference, updated code available here; http://rafb.net/p/2pqwmi63.html

[djjjozsi]

you have to fetch the results, if you still waiting one result:

$food = mysql_query("SELECT Food_Eaten1,Food_Eaten2 FROM jos_users WHERE id=".$user->id . " LIMIT 1") OR die(mysql_error());
if(mysql_num_rows($food)>0)
{
$f=mysql_fetch_assoc($food);
printf("You've eaten: %s and %s" , $f["Food_Eaten1"] , $f["Food_Eaten2"] );
}

[jc87]

Still no luck djjjozsi, I can only get it to update either Food_Eaten1 or Food_Eaten2, not both.

Any other ideas would be appreciated!

[djjjozsi]

as i see, the database structure is not the best way to add food to users.

You have a food table,
you have a users table,
make a table to store the food and users primary ID's in it.

If a user selects a new food, just insert that ID into this users_food table.

how to select the user's selected food?

SELECT * FROM users, users_food WHERE users.userID=users_food.userID

your original idea has a big problem, you need to expand the food fields to store unlimited amonts.

Why is it important to normalize your database? Then you could make unlimited relations between your users and items.

read the basics here

[Weedpacket]

jc87 wrote:

  $food_eaten1 = @mysql_query("SELECT Food_Eaten1 FROM jos_users WHERE id=".$user->id."") || die(mysql_error());

One difference between your code and djjjozsi is that you're using "||" and djjjozsi is using "or".

The difference is that "or" is evaluated after "=", but "||" is evaluated before "=" (see operator precedence).

It makes a difference because with what you have $food_eaten1 will be the result of evaluating @("SELECT Food_Eaten1 FROM jos_users WHERE id=".$user->id."") || die(mysql_error()), which will either be a boolean true, or nothing at all because the script died.

[jc87]

Thanks for your responses. I will try a different approach with the table structure suggested by djjjozsi.

[jc87]

Hey.

Had a go re-coding, see here - http://rafb.net/p/Ijg0PP69.html

But now I can search and add one food OK, it'll come up in the user_food as expected and the confirmation message will be displayed. However, if I then perform another search and 'add' a different food, the user_food_id is incremented correctly, however the food_id stays the same and instead of a new record being inserted into the table, it overwrites the old record.
For example, if I add an Apple the following will be displayed in the table -
user_food_id 1 food_id 1 user_id 64 - which is correct

If I then add another Food item, Nuts for example, the following will be displayed -
user_food_id 2 food_id 1 user_id 64 - which is incorrect as the user_food_id 1 now no longer exists and the food_id is incorrect.

Am I missing something?

[djjjozsi]

In your code you have:

...... VALUES (1, '".$_POST[fo......

In the VALUES section do not determine that user_food_id, becouse the UPDATE ON DUPLICATE still overwrite that.

Just a short resume:
This is just an example, i'm still using fakse table and field names:

you have a users table, (user_ID is primary KEY, autoincremented)
user_ID, name
1 John
2 Carl
3 Ann

You have a food table, (food_ID is primary KEY, autoincremented)
food_ID , Food
1 Apple
2 Pizza
3 Banana

You have a users_food connector table (u_f_ID is primary KEY, autoincremented)
u_f_ID , user_ID , food_ID
1 1 1
2 1 2
3 1 3
4 2 1

If the user 2 searched a new food, you insert the userid: 2 and food_ID:3 into this users_food connector table.

To avoid duplications for users to select more then one food, set a UNIQUE key to the UserID and food_ID field pairs. But in your code you need to handle the Duplicate entry error code.

[jc87]

Thanks, updated my table to Auto-Increment and changed code to -

$add_food_eaten = mysql_query("INSERT INTO user_food (food_id, user_id) VALUES ('".$_POST[foodnum]."', ".$user->id.")") OR die(mysql_error()); 

I have had to alter Food_ID to a non-unique field because I am using this for a Food Log, so in theory a user should be able to record that they have eaten a particular food a number of times. Seems to be working now though, so thanks for your input 🙂

[jc87]

OK. So as I am developing a food log, now that I can successfully add a user's food eaten to the user_food table, I am now trying to also add a date_eaten field which records, as the name suggests :p, when the food was eaten.

For this I have implemented a javascript calendar sourced from - http://www.javascriptkit.com/script/script2/timestamp.shtml

However I am having trouble passing the date selected from the calendar to my MySQL table as it appears that the $_POST['timestamp'] is not returning a value.

Right, I have been working on my Food Log including my calendar, which now works, However I am having trouble passing the date selected from the calendar to my MySQL table as it appears that the $_POST['timestamp'] is not returning a value.

Notably these parts -

<td><form name="tstest" method="post"><input type="Text" name="timestamp" value="">
<a href="javascript:show_calendar('document.tstest.timestamp', document.tstest.timestamp.value);"><img src="/joomla15/images/cal.gif" width="16" height="16" border="0" alt="Click Here to Pick up the timestamp"></a>
</form> 

$add_food_eaten = mysql_query("INSERT INTO user_food (food_id, user_id, date_eaten) VALUES ('".$_POST[foodnum]."', ".$user->id.", '".$_POST[timestamp]."')") OR die(mysql_error());

I have also tried adding a hidden field but this doesn't seem to work either.

My full module code is available here - http://rafb.net/p/XaqUtB47.html

Any ideas how I could get this to pass the value in the text field? I've tried a number of methods but nothing seems to be working at the moment.

Thanks in advance.