Solved

translate code from wpdb into mysql please?

Posted on 2014-09-03
10
176 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ah, I did not think of it that way, but I guess so  :)
0
 

Author Comment

by:St_Aug_Beach_Bum
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Author Comment

by:St_Aug_Beach_Bum
Comment Utility
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
Comment Utility
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
Comment Utility
mysqli is a little different from old-style mysql() PHP calls.
0
 

Author Closing Comment

by:St_Aug_Beach_Bum
Comment Utility
Thank you.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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 set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
The viewer will learn how to count occurrences of each item in an array.

771 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

9 Experts available now in Live!

Get 1:1 Help Now