Convert from mysql_query to $this->db->query

Quote

Post by meowmeowcastle.com » Fri Nov 20, 2015 8:30 pm

Hi,

Basically, I followed this guide to email customers on their birthday. Version 2.1.0.1

I got everything working up to the part where I need to find customers whose birthday is today. Basically it's getting the date back from the database and compare it with today's date.

Right now the code is:

Code: Select all

$findCustomers = mysql_query("SELECT * FROM rwy_customer WHERE birth_date = '" . $todayDate . "'");
while($row = mysql_fetch_array($findCustomers)){
   $birthDate = $row['birth_date'];
   $birthEmail = $row['bday_email'];
}
I got this error:

Code: Select all

Warning: mysql_query(): Access denied for user 'root'@'localhost' (using password: NO) in /home1/xxx/public_html/catalog/controller/common/header.php on line 116Warning: mysql_query(): A link to the server could not be established in /home1/xxx/public_html/catalog/controller/common/header.php on line 116Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home1/xxxx/public_html/catalog/controller/common/header.php on line 117
I figure I need to input username & pass for the connection. However, I see that

Code: Select all

 $this->db->query
doesn't need to do so. I want to switch it to something like this:

Code: Select all

$findCustomers = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE birth_date = '" . $todayDate . "'");
		while($findCustomers->rows as $result){
		   $birthDate = $result['birth_date'];
		   $birthEmail = $result['bday_email'];
}
but I got the errors:

Code: Select all

Parse error: syntax error, unexpected 'as' (T_AS) in xxxx\public_html\catalog\controller\common\header.php on line 117
How do I fix it?

If you need the whole guide I follow, it's on the link below.
http://cartadvisor.com/blog/2012/02/28/ ... -opencart/

Image
Asian Beauty in the US

User avatar
meowmeowcastle.com
New member
Posts
21
Joined
Wed Nov 18, 2015 3:03 pm
Location - Boston, MA
Top

Re: Convert from mysql_query to $this->db->query

Quote

Post by uksitebuilder » Tue Nov 24, 2015 6:48 am

change

Code: Select all

while($findCustomers->rows as $result){
         $birthDate = $result['birth_date'];
         $birthEmail = $result['bday_email'];
}
to

Code: Select all

foreach($findCustomers->rows as $result){
         $birthDate = $result['birth_date'];
         $birthEmail = $result['bday_email'];
}
However, you already know the Birthdate = Todaysdate so no need to fetch it from the DB as well.

You will end up with one email address for $birthEmail as your loop is overwriting it each iteration.

Would be better written as

Code: Select all

$birthEmails = array();
$birthDate = $todayDate;
foreach($findCustomers->rows as $result){
         $birthEmail[] = $result['bday_email'];
}
User avatar
uksitebuilder
Guru Member
Posts
7716
Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom
Top

Re: Convert from mysql_query to $this->db->query

Quote

Post by meowmeowcastle.com » Wed Nov 25, 2015 6:50 pm

Thank you for answering. I don't mind the variable get overwritten. I actually do everything inside the loop. However, my question is, what's the different between "while" and "foreach" in this case?
Why

Code: Select all

while($findCustomers->rows as $result)
won't work, but

Code: Select all

foreach($findCustomers->rows as $result)
would? (I haven't try it yet because I have a working file, but I just want to know).

Right now, the current working version is like this:

Code: Select all

$db = new mysqli('localhost', 'xxx', 'xxx', 'xxx');
		if ($db->connect_error) {
			echo "could not connect: " . $db->connect_error;
			exit();
		}
		$query = "SELECT *, SUBSTRING_INDEX(SUBSTRING_INDEX(custom_field,'\"',4),'\"',-1) AS 'Year' FROM `rwy_customer` WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(custom_field,'-',-2),'-',1) = MONTH(NOW()) AND SUBSTRING_INDEX(SUBSTRING_INDEX(custom_field,'-',-1),'\"',1) = DAY(NOW())";
		$findCustomers = $db->query($query);
		while($row = $findCustomers->fetch_assoc()){
		   $birthEmail = $row['Year'];
		   $birthday = $row['custom_field'];
               etc etc }
Nevermind the query. It's just something I use to deal with custom field in oc2.0. They stored data as json object in db and I'm not very familiar with json decode, so, it's the way it is.

But when I switch the connection to this, it breaks:

Code: Select all

$findCustomers = $this->db->query("SELECT *, SUBSTRING_INDEX(SUBSTRING_INDEX(custom_field,'\"',4),'\"',-1) AS 'Year' FROM `rwy_customer` WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(custom_field,'-',-2),'-',1) = MONTH(NOW()) AND SUBSTRING_INDEX(SUBSTRING_INDEX(custom_field,'-',-1),'\"',1) = DAY(NOW())");
		while($row = $findCustomers->fetch_assoc()){
		   $birthEmail = $row['Year'];
		   $birthday = $row['custom_field'];
etc etc }
Error:

Code: Select all

Fatal error: Call to undefined method stdClass::fetch_assoc() in /home1/meowmeo8/public_html/catalog/controller/common/header.php on line 120
So, it's basically the same error as before.

Thanks.

Image
Asian Beauty in the US

User avatar
meowmeowcastle.com
New member
Posts
21
Joined
Wed Nov 18, 2015 3:03 pm
Location - Boston, MA
Top

Re: Convert from mysql_query to $this->db->query

Quote

Post by uksitebuilder » Thu Nov 26, 2015 2:00 am

It fails because you are switching to using OpenCarts Database Classes (drivers) instead of standard mysql/mysqli

fetch_assoc() is not assigned/available from the db class as such

Hence why I specified a foreach loop in my previous answer

foreach are more efficient unless you want to meet a specific condition to break out of the loop, and as you are iterating over every row, foreach is the way to go.
User avatar
uksitebuilder
Guru Member
Posts
7716
Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom
Top

Re: Convert from mysql_query to $this->db->query

Quote

Post by meowmeowcastle.com » Fri Nov 27, 2015 3:22 pm

Thank you. It works.

Image
Asian Beauty in the US

User avatar
meowmeowcastle.com
New member
Posts
21
Joined
Wed Nov 18, 2015 3:03 pm
Location - Boston, MA
Top

Re: Convert from mysql_query to $this->db->query

Quote

Post by aleksandrx10000 » Thu Jul 12, 2018 4:21 pm

uksitebuilder wrote: ↑
Tue Nov 24, 2015 6:48 am
 change

Code: Select all

while($findCustomers->rows as $result){
         $birthDate = $result['birth_date'];
         $birthEmail = $result['bday_email'];
}
to

Code: Select all

foreach($findCustomers->rows as $result){
         $birthDate = $result['birth_date'];
         $birthEmail = $result['bday_email'];
}
However, you already know the Birthdate = Todaysdate so no need to fetch it from the DB as well.

You will end up with one email address for $birthEmail as your loop is overwriting it each iteration.

Would be better written as

Code: Select all

$birthEmails = array();
$birthDate = $todayDate;
foreach($findCustomers->rows as $result){
         $birthEmail[] = $result['bday_email'];
}
Thanks, your message helped to solve the error))
Posts
2
Joined
Sun Feb 25, 2018 3:22 am
Top
Post Reply
Return to “General Support”
Who is online

Users browsing this forum: No registered users and 275 guests