Solved

translate code from wpdb into mysql please?

Posted on 2014-09-03
10
192 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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
 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

697 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