1

I want that when someone votes for article information, it gets inserted into two tables (or run any two queries, does not matter, insert, update or select). I am using Joomla! 2.5.0 Stable.

components/com_content/models/article.php public function storeVote($pk = 0, $rate = 0)

when executing this query:

$db->setQuery( 'INSERT INTO #__content_rating ( content_id, lastip, rating_sum, rating_count )' . ' VALUES ( '.(int) $pk.', '.$db->Quote($userIP).', '.(int) $rate.', 1 )'

I want that the information in #__content table will be inserted too. How do I achieve that? I tried following, but it does not work:

$db->setQuery( 'INSERT INTO #__content_rating ( content_id, lastip, rating_sum, rating_count )' . ' VALUES ( '.(int) $pk.', '.$db->Quote($userIP).', '.(int) $rate.', 1 )'

// 'UPDATE #__content ' . ' SET testas2 = rating_sum + '.(int) $rate . ' WHERE content_id = '.(int) $pk

// 'INSERT INTO #__content ( testas2 )' . ' VALUES (7799)' );

This is picture with the syntax: http://i49.tinypic.com/1ruux0.jpg

I read about MySQL transaction, will it help me in this case? If yes, then what should the syntax should look like? Any advice is much appreciated.

Lodder
  • 19,758
  • 10
  • 59
  • 100
Edva Ptr
  • 31
  • 1
  • 9
  • Please format your code in a more readable way. – Nir Alfasi Sep 03 '12 at 08:17
  • That's a bit better :) Now, what does it mean when you say "doesn't work" ? on which line it fails ? what's the error ? in case you didn't turn on warnings and errors - you should, it will be easier to debug. – Nir Alfasi Sep 03 '12 at 08:45
  • devug was turned off. now is turn on, and I got following error: JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO edqfi_content ( testas2 ) VALUES ( 7799)' at line 1 SQL=INSERT INTO edqfi_content_rating ( content_id, lastip, rating_sum, rating_count ) VALUES ( 42, '**.***.***.***', 5, 1 )INSERT INTO edqfi_content ( testas2 ) VALUES ( 7799) and information was not inserted in tables.. what do you think? :o – Edva Ptr Sep 03 '12 at 09:11
  • I think that you should break each query into two lines, that way you'll be able to `echo` it and run the output directly in the DB for debug. for example: `$query = 'UPDATE #__content ' . ' SET testas2 = rating_sum + '.(int) $rate . ' WHERE content_id = '.(int) $pk';` And then add: `echo $query;` – Nir Alfasi Sep 03 '12 at 09:14
  • I tried: `$query1= query; $query2= query; $db->setQuery(query1,$query2);` and there I am facing issue.. `if $db->setQuery(query1,$query2)` does not work at all.. `$db->setQuery(query1);` execute only query1, but I need both $query1 and $query2.. so, how with `$db->setQuery` execute both queries..? ;/ – Edva Ptr Sep 03 '12 at 09:57
  • You can't use two queries with this function. I think you're looking for this: http://docs.joomla.org/API15:JDatabaseMySQL/queryBatch – Nir Alfasi Sep 04 '12 at 15:08
  • what about UPDATE? can I update two tables in one query? or it is same as INSERT? just can not to do that with this function? – Edva Ptr Sep 05 '12 at 23:54
  • sure: http://stackoverflow.com/a/9417254/1057429 – Nir Alfasi Sep 06 '12 at 01:43
  • thanks for helping me. this is the syntax which I am using: http://i49.tinypic.com/2q06ae8.jpg but I get following error. maybe you see, where is a mistake? – Edva Ptr Sep 06 '12 at 03:03
  • `JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'edqfi_content.testas2=rating_sum + 3 WHERE edqfi_content_rating.content_id = 13A' at line 1 SQL=UPDATE edqfi_content_rating , edqfi_content SET edqfi_content_rating.rating_count = rating_count + 1, edqfi_content_rating.rating_sum = rating_sum + 3, edqfi_content_rating.lastip = '88.119.189.154'edqfi_content.testas2=rating_sum + 3 WHERE edqfi_content_rating.content_id = 13AND edqfi_content.id= 13` – Edva Ptr Sep 06 '12 at 03:03

1 Answers1

0

Try to run the following directly in the DB (use phpAdmin):

UPDATE edqfi_content_rating , edqfi_content 
SET edqfi_content_rating.rating_count = edqfi_content_rating.rating_count + 1, 
edqfi_content_rating.rating_sum = edqfi_content_rating.rating_sum + 3, 
edqfi_content_rating.lastip = '88.119.189.154',
edqfi_content.testas2=edqfi_content_rating.rating_sum + 3 
WHERE edqfi_content_rating.content_id = 13
AND edqfi_content.id= 13

and see if you get any errors.

Nir Alfasi
  • 53,191
  • 11
  • 86
  • 129
  • yes, I got: `1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'edqfi_content.testas2=edqfi_content_rating.rating_sum + 3 WHERE edqfi_content_' at line 5` something wrong with edqfi_content.testas2=edqfi_content_rating.rating_sum ;/ any ideas? thanks – Edva Ptr Sep 06 '12 at 22:32
  • A comma was missing after `'88.119.189.154'` - please try again – Nir Alfasi Sep 06 '12 at 22:59
  • yes, now was fine. so what it says for us? in syntax I replaced #__content with edqfi_content and #__content_rating with edqfi_content_rating and now it looks: `$db->setQuery( 'UPDATE edqfi_content_rating , edqfi_content' . 'SET edqfi_content_rating.rating_count = rating_count + 1, edqfi_content_rating.rating_sum = rating_sum + '.(int) $rate. ', edqfi_content_rating.lastip = '.$db->Quote($userIP) . 'edqfi_content.testas2=rating_sum + '.(int) $rate. 'WHERE edqfi_content_rating.content_id = '.(int) $pk. 'AND edqfi_content.id= '.(int) $pk );` any ideas? – Edva Ptr Sep 06 '12 at 23:22
  • forgot to mentioned, that it still get error: `JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'edqfi_content.testas2=rating_sum + 3WHERE edqfi_content_rating.content_id = 34AN' at line 1 SQL=UPDATE edqfi_content_rating , edqfi_content SET edqfi_content_rating.rating_count = rating_count + 1, edqfi_content_rating.rating_sum = rating_sum + 3, edqfi_content_rating.lastip = 'IP'edqfi_content.testas2=rating_sum + 3WHERE edqfi_content_rating.content_id = 34AND edqfi_content.id= 34` – Edva Ptr Sep 06 '12 at 23:27
  • I can't read this code in the comments - too much hassle. please update the question. when you say "now was fine" do you mean that the query ran fine when you ran it directly in the DB but it returns an error when you try running it with `$db->setQuery` ? – Nir Alfasi Sep 06 '12 at 23:41
  • Like I said: I think that you should break each query into two lines, that way you'll be able to echo it and run the output directly in the DB for debug. for example: `$query = 'UPDATE #__content ' . ' SET testas2 = rating_sum + '.(int) $rate . ' WHERE content_id = '.(int) $pk';` And then add: `echo $query;` -grab the output of `echo $query;` and run it directly in the DB - if it works fine, there's no reason why `$db->setQuery($query)` won't work. – Nir Alfasi Sep 07 '12 at 00:00
  • no, it was a syntax error. I fixed. thank you for your assistance :) – Edva Ptr Sep 07 '12 at 00:38