[SOLVED] PHP Form Plugin : Query which doesn't work

[marcq]

Hi,

I'm trying to insert into / update the Acymailing (joomla extension) subscriber table some datas after data stored before calculations. But the following nasty message is displayed :

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 'IF EXISTS (SELECT name, email FROM gprh_acymailing_subscriber WHERE email = 'cli' at line 1 SQL=IF EXISTS (SELECT name, email FROM gprh_acymailing_subscriber WHERE email = 'client1-gprh-ce@webamazingapps.com') UPDATE gprh_acymailing_subscriber SET name = 'Membre 1 - CE', email = 'client1-gprh-ce@webamazingapps.com'; ELSE INSERT INTO gprh_acymailing_subscriber (name, email) VALUES ('Membre 1 - CE', 'client1-gprh-ce@webamazingapps.com')

I've checked the sql syntax (+ with a online syntax checker), but it seems that the code is ok.

So if someone could have a look at my code would be great :

$query = $db->getQuery(true);
$query = "IF EXISTS (SELECT name, email FROM gprh_acymailing_subscriber WHERE email = '$ce_email_business')
UPDATE gprh_acymailing_subscriber
SET name = '$ce_nom', email = '$ce_email_business'
WHERE email = '$ce_email_business';
ELSE
INSERT INTO gprh_acymailing_subscriber (name, email) VALUES ('$ce_nom', '$ce_email_business')";
$db->setQuery($query);
$db->execute();

Thanks a lot in advance, cheers,

Marc

 

[marcq]

I tried this :
First check if the value (email) exists
If value = 1 then update
If value = 0 then insert

But again, it doesn't work :

SELECT SQL_CALC_FOUND_ROWS name, email FROM gprh_acymailing_subscriber WHERE email = 'mquent@got.fr';
SET @rows = FOUND_ROWS();
IF(@rows=0)
    INSERT INTO gprh_acymailing_subscriber (name, email) VALUES ('mquent', 'mquent@got.fr')
ELSE
    UPDATE gprh_acymailing_subscriber SET name = 'mquent', email = 'mquent@got.fr' WHERE email = 'mquent@got.fr';

I tested it directely in Phpmyadmin, but got an error again :

#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 'IF(@rows=0)
INSERT INTO gprh_acymailing_subscriber (name, email) VALUES ('mqu' at line 1

I guess that it has to do with "(@rows=0)"!?

 

[troester]

AFAIK
IF EXISTS (...) is not MySQL (I think it's SQL Server) and $db->query is for single MySQL queries only, you can't use multiple ones.

Split your queries and use php to check the conditions .

 

[marcq]

of course ! I understand now why nothing works... Thanks troester

 

[marcq]

1 step
I would like to check if an email address stored in the "gprh_fabrik_user_registration" table is already stored in the "gprh_acymailing_subscriber" table.

2 sept
According the returned value I would like to update the record or insert a new record in the "gprh_acymailing_subscriber" table

I guess that the best way could be to use "num_rows".

If "num_rows" == 0 : insert the email and name fields value in the "gprh_acymailing_subscriber" table
Else "num_rows" == 1 : update the existing record in the "gprh_acymailing_subscriber" table

I wrote something, but it doesn't work because I can't find anything explaining the correct syntax/structure of the query to use :

$result = mysql_query("SELECT email FROM gprh_acymailing_subscriber WHERE email = '$ce_email_business'");
$num_rows = mysql_num_rows($result);
 
if ($num_rows == 1) {
  $query = "UPDATE gprh_acymailing_subscriber SET name = '$ce_nom', email = '$ce_email_business' WHERE email = '$ce_email_business')";
}
else {
  $query = "INSERT INTO gprh_acymailing_subscriber (name, email) VALUES ('$ce_nom', '$ce_email_business')";
 
}

would be great if someone could give me a tips...

 

[marcq]

Is it better ?

$db = JFactory::getDbo();
/* GET NAME AND EMAIL VALUES FOR ACYMAILING INTERMEDIARY TABLES */
$ce_nom=$data['gprh_fabrik_user_registration___ce_nom'];
$ce_email_business=$data['gprh_fabrik_user_registration___ce_email_business'];
$ce_listid=2;
/* COUNT */
$query = $db->getQuery(true);
$query->select('COUNT(*)')->from('gprh_acymailing_subscriber')->where(email = '$ce_email_business');
$db->setQuery($query);
$count = $db->loadResult();
 
if ($count === 1)
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_acymailing_subscriber SET name = '$ce_nom', email = '$ce_email_business' WHERE email = '$ce_email_business')";
$db->setQuery($query);
$db->execute();
}
 
if ($count === 0)
{
$query = $db->getQuery(true);
$query = "INSERT INTO gprh_acymailing_subscriber (name, email) VALUES ('$ce_nom', '$ce_email_business')";
$db->setQuery($query);
$db->execute();
}

 

[marcq]

Issue solved, thanks to troester's availability and always good tips !

$db = JFactory::getDbo();
 
/* GET NAME AND EMAIL VALUES FOR ACYMAILING */
$ce_nom=$data['gprh_fabrik_user_registration___ce_nom'];
$ce_email_business=$data['gprh_fabrik_user_registration___ce_email_business'];
$ce_listid=2;
 
/* COUNT CE */
$query = $db->getQuery(true);
$query = "SELECT COUNT(*)
          FROM gprh_acymailing_subscriber
          WHERE email = '$ce_email_business'";
$db->setQuery($query);
$count = $db->loadResult();
 
dump($count, 'count');
dump($ce_email_business, 'email_business');
 
/* LAUNCH EC QUERY ACCORDING COUNT VALUE 0 INSERT AND VALUE 1 UPDATE */
if ($count === '1')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_acymailing_subscriber SET name = '$ce_nom', email = '$ce_email_business' WHERE email = '$ce_email_business'";
$db->setQuery($query);
$db->execute();
}
 
if ($count === '0')
{
$query = $db->getQuery(true);
$query = "INSERT INTO gprh_acymailing_subscriber (name, email) VALUES ('$ce_nom', '$ce_email_business')";
$db->setQuery($query);
$db->execute();
}