#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0

    Converting from mysql to mysqli


    I have recently been converting a site to mysqli but one statement I have not been able to find a conversion for.
    Code:
    $stats  = dbQuery("SHOW TABLE STATUS FROM $dbname LIKE '$dbprefix%'");
    Anyone have any knowledge on this one?
    Thank you kindly,
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,143
    Rep Power
    9398
    dbQuery() is a function you have defined somewhere in your code. Fix it.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0
    dbQuery() is a function you have defined somewhere in your code. Fix it.
    The function is not the issue, the "show table status" is not understood by mysqli. I am looking for the alternative, if there is one.
    Thanks,
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    MySQLi doesn't understand any query. All it does is send data from PHP to the MySQL database system and back. It does not parse or process the queries in any way.

    So my first question would be: What makes you think that MySQLi doesn't accept this particular query? If you get an error message, now is the time to tell us.

    Secondly, what exactly are you trying to do? I can hardly think of a legitimate use case for "meta queries" like that. They're meant for database administrators, not for web applications.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    Do you have some sort of error to prove that? What do you mean it doesn't understand? Did you get the "1004 Can't Understand" error?
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0
    Hi Jacques,

    What I am trying to do is convert a third party site search to mysqli format. This part is for the administration of the search database. I have converted all aspects of the search with the exception of the aforementioned statement.

    Using mysqli it produces errors like:
    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 'LIKE '%'' at line 1
    until I trim the statement back to simply "SHOW TABLES", then it goes through but does not give any of the "status" information that it does when in mysql mode.

    What it is suppose to retrieve is:
    a list of tables
    how many rows in each table
    creation date
    data size
    index size

    Thanks,
  12. #7
  13. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,143
    Rep Power
    9398
    $dbname is probably missing or empty.

    I'll say it again because you probably skipped over it as some sort of dismissal: dbQuery() is not a function from the mysql extension. You cannot convert the call to use mysqli because it does not call a mysql function. The function does, however, somewhere in its source code, call a mysql function. That is what you fix. Not dbQuery() unless you really want to because that is not where the problem is.

    Comments on this post

    • Scorpa54 agrees
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by Scorpa54
    Using mysqli it produces errors like:
    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 'LIKE '%'' at line 1
    Well, that's a MySQL error, not an error from the MySQLi extension. In other words, the problem is caused by you sending a wrong query to MySQL.

    If you look at the query in the error message, there's obviously an issue with the variables being empty (as requinix has already pointed out). You need to debug your code. The first and most obvious step would be to output the query and see what exactly it says. Then you investigate why the variables are not being set.

    Either way, this has nothing to do with MySQLi. Just because the error has happened while switching to MySQLi doesn't mean that it was caused by MySQLi (that's a Post hoc ergo propter hoc fallacy).



    Originally Posted by requinix
    dbQuery() is not a function from the mysql extension.
    I'm pretty sure he/she is aware of that.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0
    I not sure where I am confusing this so lets try this approach.
    Code:
    $stats = mysql_query("SHOW TABLE STATUS FROM $dbname LIKE '$dbprefix%'");
    works perfectly,
    Code:
    $stats = mysqli_query("SHOW TABLE STATUS FROM $dbname LIKE '$dbprefix%'");
    does not.
    From everything I have searched there is no "SHOW TABLE STATUS" in mysqli, I am looking for what will, hopefully, get the same results.
    Thanks,
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0
    Originally Posted by requinix
    $dbname is probably missing or empty.

    I'll say it again because you probably skipped over it as some sort of dismissal: dbQuery() is not a function from the mysql extension. You cannot convert the call to use mysqli because it does not call a mysql function. The function does, however, somewhere in its source code, call a mysql function. That is what you fix. Not dbQuery() unless you really want to because that is not where the problem is.
    It was not my intent to make you feel as if I had simply dismissed you. The dbQuery() function is a call to mysqli:

    Code:
    $conn = new mysqli($host, $user, $pass, $dbase);
    if($conn->connect_errno){
    	die('No connection: ' . $conn->connect_error);
    }
    
    function dbQuery($sql){
    	global $conn;
    	$result = $conn->query($sql) or die($conn->error);
    	return $result;
    }
    Thanks,
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by Scorpa54
    Code:
    $stats = mysql_query("SHOW TABLE STATUS FROM $dbname LIKE '$dbprefix%'");
    works perfectly,
    Code:
    $stats = mysqli_query("SHOW TABLE STATUS FROM $dbname LIKE '$dbprefix%'");
    does not.
    The second variant isn't even valid, because mysqli_query() requires two arguments.

    Are you sure you're giving us the real code?



    Originally Posted by Scorpa54
    From everything I have searched there is no "SHOW TABLE STATUS" in mysqli
    I'll say it again: MySQLi is a database interface, not a database system.

    It does not hold databases, process queries or anything like that. It takes the query string and sends it to MySQL. That's it. The actual processing is up to MySQL. You don't even need MySQLi. You could (theoretically) communicate directly with MySQL.

    I've actually just executed a SHOW TABLE STATUS query with MySQLi, and it worked just as well as with any other database interface.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2009
    Location
    Nebraska, USA
    Posts
    876
    Rep Power
    275
    in my reading from HERE and HERE, mysqli_query() requires 2 parameters, first being the connection, 2nd is the SQL statement.

    like so:
    Code:
    $stats = mysqli_query($conn, "SHOW TABLE STATUS FROM $dbname LIKE '$dbprefix%'");
    EDIT: ah poo, Jacques1 beat me to it.
    Last edited by DonR; December 6th, 2013 at 05:39 PM.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0
    Okay, I must now come forth with a sheepish grin and eat crow.
    The error was that the search was picking up its variable $dbname from an old variable that I had changed the name of. Of course the only spot in pages of code that uses the old variable.
    My sincerest apologies and thanks for trying to get it through my thick skull.
  26. #14
  27. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Glad to hear you've fixed it.

    The conclusion you should draw from this is that it's a good idea to analyze bugs, not make assumptions about them. You've spent several days chasing a MySQLi problem that only existed in your mind. If you had actually checked the string as we suggested, it probably would have taken only a few minutes to find out that the variable is indeed empty.

    Always start by gathering hard facts. You don't need a special debugger for that. A simple var_dump() is enough.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo