Solved

translate code from wpdb into mysql please?

Posted on 2014-09-03
10
179 Views
Last Modified: 2014-09-07
Ugh, I haven't worked with mysql in forever and didn't know that much to begin with.

I've got this code in my wordpress theme:

========================

//get overall count for use later in script

$overall = $wpdb->get_var("SELECT overall FROM adshare WHERE author = '$author_id'");

//if new author, add them into db and start count at 1 for rest of script

       if (!$overall){      
      $insert = $wpdb->insert( 'adshare', array( 'author' => $author_id));
      $overall = '1';
      }

//if not new, just add this hit to to count

       else { $wpdb->query("UPDATE adshare SET overall = overall+1 WHERE author = '$author_id'"); }

// reset large numbers as there's no need for them

 if ($overall >= '9999'){ $wpdb->query("UPDATE adshare SET overall = 1 WHERE author = '$author_id'"); }

===========================

I'm moving this script outside of the main wordpress flow of things and want to do this same thing with mysql, not using the wpdb.

Can you translate this script for use directly with mysql?

I've got a connection established, but not sure how to adapt these, my attempts have been fruitless.

=========================

$con = mysqli_connect("localhost", "me", "password","database");

if (mysqli_connect_errno()) {

//do this (alternate script if there is a db problem)

}

else {

//mysql code for getting overall count, adding a row if no author listed, incrementing count


}
0
Comment
Question by:St_Aug_Beach_Bum
  • 7
  • 3
10 Comments
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 40302251
For the most part, all wpdb() does is provide a sanitizing wrapper around the mysql queries.  So you already have the syntax:

SELECT overall FROM adshare WHERE author = '$author_id'

UPDATE adshare SET overall = overall+1 WHERE author = '$author_id'

UPDATE adshare SET overall = 1 WHERE author = '$author_id'

The only one that needs any sort of thinking is this one:



//if new author, add them into db and start count at 1 for rest of script

if (!$overall){      
      $insert = $wpdb->insert( 'adshare', array( 'author' => $author_id));
      $overall = '1';
      }

Open in new window


The insert() command takes the following arguments:

table, data, format (optional)

So the above is adding a new author ID number to the table adshare.  A simple insert operation...
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 40304761
Sooo... something like this?

------------------------------------
$author_id = '5'; //testing

//mysql code for getting overall count, adding a row if no author listed, incrementing count

$query = mysqli_query("SELECT overall FROM adshare WHERE author = '$author_id'",$con);

$overall = mysqli_result ($query);

echo $overall;
------------------------------------

But that doesn't work (I get a blank for 'echo $overall'). I know, I'm lost on mysql. All the examples I seem to find are for fetching rows and arrays. I just want this one thing.

Chris
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 40304877
So this really isn't MySQL you're struggling with, it's the PHP to call it?
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 40306415
Ah, I did not think of it that way, but I guess so  :)
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 40306680
I was giving this as a possible solution elsewhere -

echo array_values(mysqli_fetch_array($mysqli->query("SELECT overall FROM adshare WHERE author='5'")))[0];

but I get nothing echoed.

The connection seems to be ok, no error messages. Why can't I get anything from this?

Full script I'm trying to figure out now...

-----------------------------------------------------------------------------------------

$mysqli = new mysqli("localhost", "me", "pass", "fstest");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
}

else {

echo array_values(mysqli_fetch_array($mysqli->query("SELECT overall FROM adshare WHERE author='5'")))[0];

}

------------------------

I get nothing.

If I go to phpmyadmin and go to the section where I can do an "SQL query on database fstest:", and I enter:

SELECT overall FROM adshare WHERE author='5'  

I get the result, no problem. why can't I get it with this script?

Chris
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:St_Aug_Beach_Bum
ID: 40306688
I set my server to display php errors, I get nothing there.
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 40306728
If I do something like this...

--------------
$result = mysqli_query("SELECT overall FROM adshare WHERE author = '5'");

echo $result;
-------------

I should get something about a resource ID, shouldn't I? I don't get anything with that either.
0
 

Author Comment

by:St_Aug_Beach_Bum
ID: 40306745
Ok, finally got it using this:

$overall = $mysqli->query("SELECT overall FROM adshare WHERE author = '5'")->fetch_object()->overall;
echo $overall //now works

adapted from this site:

http://cameronspear.com/blog/get-a-single-value-from-mysql-database-in-one-line-of-php/

but I wonder why these other snippets would not work...?
0
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 500 total points
ID: 40306935
mysqli is a little different from old-style mysql() PHP calls.
0
 

Author Closing Comment

by:St_Aug_Beach_Bum
ID: 40308549
Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
phpmyadmin 3 33
Getting Column Height to Match Other Column 5 44
Session timeout 5 14
Could you possibly point why this Codeigniter method is been called twice? 7 25
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The viewer will learn how to dynamically set the form action using jQuery.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now