Drop MySQL table in a PHP program

[tiggsy]

I've been going round and round trying to sort this.

I have this in one of my programs:

drop table if exists temp2;
rename table something to temp2;
rename table temp to something;
create table temp like something

and it's not working, though it will work when I manually paste it into the sql box on phpMyadmin

The problem is probably the first line: "drop table..." requires a confirmation, but as the program concerned is a cron job run once a day at 3am (to update a site), this is not available. I haven't been able to find any workaround despite lengthy searching. So I'm reduced to manually running these commands every morning, making the update late (which is probably not all that serious, but is a pain in the ass).

Anybody?

[nileshgr]

Please share your PHP code also.

[tiggsy]

The code is:

//rename the old database
$query5="drop table if exists temp2;
rename table something to temp2;
rename table temp to something;
create table temp like something";
//drop old database but not till testing is complete
/*"drop table temp2";*/
$result5 = mysql_query($query5);

[shubhamjain1]

http://dev.mysql.com/doc/refman/5.0/en/drop-table.html

There is no such thing written as for confirmation. May be your user doesnt have DROP privelages.

[nileshgr]

http://dev.mysql.com/doc/refman/5.0/en/drop-table.html

There is no such thing written as for confirmation. May be your user doesnt have DROP privelages.

Yeah exactly, even I was wondering how he's saying that it requires confirmation. But the contradiction is that, it works from PMA.

@tiggsy, did you connect to the mysql server with proper parameters in mysql_connect() ?

[tiggsy]

Yes the user (me) does have all privileges, including drop

You try dropping a table - you will get a message "Are you sure?" I need to say "Yes" without being there!

[nileshgr]

Yes the user (me) does have all privileges, including drop

You try dropping a table - you will get a message "Are you sure?" I need to say "Yes" without being there!

Friend, that question which PMA asks is a configurable option.

The code you shared, doesn't contain any mysql_connect() or you've not shared it here ?

You might be missing something in mysql_connect or the complete statment itself. Try again, also once connected, specify the connection resource explicitly in mysql_query()

$con = mysql_connect($server, $user, $pass, $db)
$res = mysql_query($query, $con)

[tiggsy]

This is a small portion of a larger program. The mysql connection is at the beginning before 4 other queries, all of which work fine. I've been writing php programs for several years, and normally have no problems sorting out problems. This one has beaten me.

How do i configure the option so it doesn't ask for confirmation? I'm on a cpanel host.

[tiggsy]

Oh wth, here's the whole thing:

#!/usr/bin/php
<?php
function getad($url) {
if (ini_get('allow_url_fopen') == '1') {
  $content = file_get_contents($url);
  if ($content !== false) {
    // do something with the content
    //my host doesn't allow this, so can't be bothered
    } 
  else {
    $content = getcurlad($url);
    }
  } 
else {
  $content = getcurlad($url);
  }
echo "$url: $content<br />";
return $content;
}

function getcurlad($url) {
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.5) Gecko/20041107 Firefox/1.0');
$content = curl_exec($ch);
curl_close($ch);
return $content;
}

function GetRemoteLastModified($url) {
$headers = get_headers($url,1);
return $headers['Last-Modified'];
}

$monthnames = array(1 => "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
function LongDateToU($thisdate) {
/* Takes a date in the format Wed, 08 Jan 2003 23:11:55 GMT (RFC2822 format)
and gets seconds since Unix Epoch 
**NOTE**: ignores timezone as I only use it for files on same server */
$pieces = explode(" ",$thisdate);
global $monthnames;
$d = $pieces[1]+0;
$m = $pieces[2];
for ($q=1;$q<=12;$q++) {
  if ($m == $monthnames[$q]) {
    $m = $q;
    }
  }
$y = $pieces[3]+0;
$fulltime = $pieces[4];
unset($pieces);
$pieces = explode(":",$fulltime);
$hr = $pieces[0]+0;
$min = $pieces[1]+0;
$sec = $pieces[2]+0;
return date("U",mktime($hr, $min, $sec, $m, $d, $y));
}

function savead($adname, $content) {
$wfile = "./ads/".$adname;
echo "creating $wfile<br />\n";
$handle = fopen($wfile,'w');
if (fwrite($handle,$content) === FALSE) {
	//notify me
    mail($adminEmail,"Failure creating ad file on DIP","On DIP record $couponId, the ad file could not be created\n\nRobot35 with love");
	}
fclose($handle);
}

$script_root = "./";
$adminEmail = "[e-mail]";
$allcoupons = array();
$dbServer='localhost';
$dbUser='ausername';
$dbPass='apassword';
//pass 0 - us site
mysql_connect("$dbServer", "$dbUser", "$dbPass") or die("Could not connect");
echo "Connected successfully<br />";
//pick up and store the data
$srchstr = array("1234567","3456789","1425852","u=4044","qrABC11IRks","0gAFgh2vvLQ","gnid=72311");
$replstr = array("7654321","9876543","1420158","u=16578","edip0q*M7qs","eLem0q*M7qs","gnid=78241");
for ($p=0;$p<2;$p++) {
  if ($p == 0) {
    mysql_select_db("user_ussite") or die("Could not select database ARN");
    }
  else {
    mysql_select_db("user_uksite") or die("Could not select database SMM");
    }
  echo "<br /><br />Database $p selected successfully<br />";
  $query="SELECT * FROM `coupons` where sector='handheld' and ExpiryDate>=CURRENT_DATE";
  $result=mysql_query($query);
  if ($result != FALSE) {
    $numrows=mysql_num_rows($result);
    }
  else {
    $numrows=0;
    }
  echo "$numrows records in database $p<br />";
  for ($j1=0;$j1<$numrows;$j1++) {
    $allcoupons[$j1]=mysql_fetch_assoc($result);
    $allcoupons[$j1]['CouponId'] = $allcoupons[$j1]['CouponId']+9000;
    $allcoupons[$j1]['CouponName'] = mysql_real_escape_string($allcoupons[$j1]['CouponName']);
    $allcoupons[$j1]['ShortName'] = mysql_real_escape_string($allcoupons[$j1]['ShortName']);
    $allcoupons[$j1]['CouponCode'] = mysql_real_escape_string($allcoupons[$j1]['CouponCode']);
    echo "<br />original url: ";
    echo $allcoupons[$j1]['CouponUrl'];
    $allcoupons[$j1]['CouponUrl'] = mysql_real_escape_string(str_replace($srchstr,$replstr,$allcoupons[$j1]['CouponUrl']));
    echo "<br />now: ";
    echo $allcoupons[$j1]['CouponUrl'];
    $allcoupons[$j1]['ImageUrl'] = mysql_real_escape_string(str_replace($srchstr,$replstr,$allcoupons[$j1]['ImageUrl']));
    $allcoupons[$j1]['ImageAlt'] = mysql_real_escape_string($allcoupons[$j1]['ImageAlt']);
    $allcoupons[$j1]['metaDesc'] = mysql_real_escape_string($allcoupons[$j1]['metaDesc']);
    $allcoupons[$j1]['tags'] = mysql_real_escape_string($allcoupons[$j1]['tags']);
    $allcoupons[$j1]['Live'] = 'no';
    $allcoupons[$j1]['TrafficBugged'] = 'no';
    $allcoupons[$j1]['ImageOptim'] = '';
    $allcoupons[$j1]['HandPicked'] = '0000-00-00';
    $allcoupons[$j1]['LastTweet'] = '0000-00-00';
    echo "$j1 read, ";
    #print_r ($allcoupons[$j1]);
    }
 mysql_select_db("user_handheld") or die("Could not select database DIP (1st)");
  echo "<br />Database DIP selected<br />";
//make a temp database
  if ($p == 0) {
    $query2="CREATE TABLE temp LIKE coupons";
    $result2=mysql_query($query2);
    echo "<br />TEMP DATABASE CREATED<br />";
    }
  //add all the stuff to it
  for ($j=0;$j<$numrows;$j++) {
    if (isset($allcoupons[$j]['CouponId'])) {
      //record may have been deleted because DIP is not approved for this campaign
      $query3 = "insert into temp (`CouponId`, `CouponName`, `ShortName`, `CouponCode`, `CouponUrl`, `ImageUrl`, `ImageAlt`, `CouponAd`, `metaDesc`, `tags`, `sector`, `business`, `country`, `StartDate`, `StartTime`, `ExpiryDate`, `ExpiryTime`, `SomeDaysOnly`, `isAdult`, `Live`, `lastTweet`, `HandPicked`, `real_expiry`, `TrafficBugged`, `ImageOptim`) VALUES ({$allcoupons[$j]['CouponId']}, '{$allcoupons[$j]['CouponName']}', '{$allcoupons[$j]['ShortName']}', '{$allcoupons[$j]['CouponCode']}', '{$allcoupons[$j]['CouponUrl']}', '{$allcoupons[$j]['ImageUrl']}', '{$allcoupons[$j]['ImageAlt']}', '{$allcoupons[$j]['CouponAd']}', '{$allcoupons[$j]['metaDesc']}', '{$allcoupons[$j]['tags']}', '{$allcoupons[$j]['sector']}', '{$allcoupons[$j]['business']}', '{$allcoupons[$j]['country']}', '{$allcoupons[$j]['StartDate']}', '{$allcoupons[$j]['StartTime']}', '{$allcoupons[$j]['ExpiryDate']}', '{$allcoupons[$j]['ExpiryTime']}', '{$allcoupons[$j]['SomeDaysOnly']}', '{$allcoupons[$j]['isAdult']}', '{$allcoupons[$j]['Live']}', '{$allcoupons[$j]['lastTweet']}', '{$allcoupons[$j]['HandPicked']}', '{$allcoupons[$j]['real_expiry']}', '{$allcoupons[$j]['TrafficBugged']}', '{$allcoupons[$j]['ImageOptim']}')";
      $result3=mysql_query($query3);
      #echo "$j written: $query3,<br />";
      }
    }
  //clear data from previous pass
  unset($allcoupons);
  }
$query1 = "select * from temp order by CouponAd";
$result1 = mysql_query($query1);
$numrows1 = mysql_num_rows($result1);
echo "<br />$numrows1 rows in combined table<br />";
//grab ads and modify content AND main links for cj/trd etc (where sites have individual ids)
//pickup stuff we just stored to sort out site ids
for ($j0=0;$j0<$numrows1;$j0++) {
  $row = mysql_fetch_array($result1);
  $couponId = $row['CouponId'];
  $adname = $row['CouponAd'];
  $country = $row['country'];
  if ($adname == '') {
    //notify me
    echo "{$couponId} has no ad!<br />";
    mail($adminEmail,"Missing ad file on DIP","On DIP record $couponId, the ad file name is  missing\n\nRobot35 with love");
    }
  else {
    echo "Looking at ad $adname: ";
    $adsource = ($country == "UK") ? "http://www.somesite.co.uk/ads/" : "http://www.asomesite.com/ads/";
    $remotemoddate = LongDateToU(GetRemoteLastModified($adsource.$adname));
    echo "remote: ".$remotemoddate.", ";
    if (file_exists("./ads/".$adname)) {
      $localmoddate = filemtime("./ads/".$adname);
      }
    else {
      //dummy date
      $localmoddate = date("U",mktime(0,0,0,1,1,2000));
      }
    echo "local: ".$localmoddate.", ";
    if ($remotemoddate>$localmoddate) {
      echo "New ad needed<br />";
      mail($adminEmail,"Adcheck required on DIP","On DIP, the ad $adname is required/needs an update\n\nRobot35 with love");
      }
    else {
      echo "Existing ad ok<br />";
      }
    }
  }
//rename the old database
$query5="drop table if exists temp2;
rename table coupons to temp2;
rename table temp to coupons;
create table temp like coupons";
//drop old database but not till testing is complete
/*"drop table temp2";*/
$result5 = mysql_query($query5);
mail($adminEmail,"DIP Update Complete","I have completed the daily update on DIP\n\nRobot35 with love");
include "./coupon-update.php";
echo "<br />\nUpdate complete!";
?>

[nileshgr]

I didn't ask the whole code man !
Just the database part.
Anyway, the code seems absolutely fine, I don't see any problems in it.
At the point where you set $dbUser, etc. use single quotes to specify that and remove double quotes while specifying variables in mysql_connect; though may not make any difference unless you have an special characters in the password (like $).

[tiggsy]

I thought it was easier to paste the whole thing in, rather than get asked for disparate bits repeatedly, and anyway, it's basically all about the database one way or another.

If i replace

mysql_connect("$dbServer", "$dbUser", "$dbPass") or die("Could not connect");

with

mysql_connect('$dbServer', '$dbUser', '$dbPass') or die("Could not connect");

the values in the variables won't be inserted, but the literal characters, eg. $dbUser will be sent instead.

The point where it's set is already in single quotes:

$dbServer='localhost';
$dbUser='ausername';
$dbPass='apassword';

Anyway, everything before the problem code (drop table) works fine, so it's obviously not a problem the way it is.

[nileshgr]

You misunderstood my post. I said use single quotes only while assiging values to the variables.
Obviously, if you use single quotes and place variable names inside them, it would be passed literally.

I meant something like this:

$dbUser = 'user';
...
mysql_connect($dbUser...);

[tiggsy]

Yeah, that would work, as well.

But how do i change the configuration so that it no longer asks for confirmation of a drop table?

[nileshgr]

Yeah, that would work, as well.

But how do i change the configuration so that it no longer asks for confirmation of a drop table?

Dude you're confusing PHPMyAdmin and the MySQL API. The confirmation for drop table, is a PHPMyAdmin configuration option. It has nothing to do with PHP and/or MySQL.

Any queries given from the MySQL command line or any sort of API do not require any sort of confirmation. They are just executed.

You need to check the permissions of the mysql user you're using in the script. But you say, it works from PHPMyAdmin. I am confused about this.

Tell me if PHPMyAdmin is running on localhost or it is somewhere on the network and is handling the MySQL server via TCP ?

[tiggsy]

The confirmation thing was a guess. The only reason I could see that it wouldn't work.

The 3 databases used in the program are all on a cpanel host, the server is addressed as "localhost" on that server. Not sure what you mean by a network. sfaik the mysql server is a different one to the one on which the sites are hosted, but localhost is the address, so i don't know if that makes any difference.

In this program i am using the same user as i use when i log in through phpMyadmin, and all the permissions are therefore the same. I only use it for admin/backroom stuff, so it has all privileges, in contrast to the user on the live site/s.

It seems extremely odd that everything works up to this point in the script. This must be the point where it all falls over, but I can't work out why. If it's not the confirmation - and it's definitely not the permissions - then what else could it be?

[nileshgr]

MySQL can be configured with same username under different hosts with different permissions.

I.e., if tiggsy is the mysql username, permissions can be different for tiggsy@localhost and tiggsy@192.168.1.2
something of that sort. If PHPMyAdmin is hosted on 192.168.1.2 then logging in as tiggsy from PHPMyAdmin will take tiggsy@192.168.1.2's permissions which may not be same as tiggsy@localhost.

From my point of view, the problem is something like this I feel. PHPMyAdmin is running on an host other than the mysql server, and is controlling the mysql server via TCP.

Where as you're executing your script on localhost.

[tiggsy]

PHPMyAdmin is at the hosting company, not on my home computer (192.168.1.2 is my computer, right?) I do have phpmyadmin at home, but it doesn't access the databases on the server, just development stuff here.

But even if it is the case that I'm accessing the database servers from different addresses - firstly, how do i find these out, secondly how do i set permissions for the different addresses (bearning in mind that in cpanel, you add users, not users+addresses, and sfaik, there's no other way to change users/permissions etc)

[rajarajan2017]

PHPMyAdmin is at the hosting company, not on my home computer (192.168.1.2 is my computer, right?) I do have phpmyadmin at home, but it doesn't access the databases on the server, just development stuff here.

Yes, your hosting company had a seperate server to host your site online. so you have to upload all files and database to the server.

[tiggsy]

Everything is already on the server/s. My problem is:

Why is mysql refusing to drop a table from a php query inside a program (unattended) for a user with Drop privileges, and how do i get it to do so

[rajarajan2017]

Is it drop query works in your phpmyadmin sql editor?

[tiggsy]

Yes, drop query works in phpmyadmin with the same user. However it requests confirmation beforehand. I'm told that the Mysql api does not require confirmation when dealing with queries from php. But it still fails at that point, so I'm looking for a reason/fix

[nileshgr]

Okay friend, lets do some debugging.
Do you have CREATE USER and GRANT permission ?
If yes, execute this:

CREATE USER 'demo'@'%' IDENTIFIED BY 'SOME_PASSWORD';
GRANT ALL ON *.* to 'demo'@'%';

Now try to drop the table using this new user demo. It should work fine. And this will prove what's problem is. I still think its something to do with same username and different permissions with corresponding hosts.

[Manuz]

Hey,
Try to find out bug by mysql_error. Use

$result5 = mysql_query($query5) or die(mysql_error());

Then find out what the error tells.

[tiggsy]

itech7: on cpanel hosts, the only way to create a user and grant permissions is through cpanel, not with commands in mysql

[tiggsy]

Manuz: Ye gods. Why didn't I think of this? why did nobody else suggest it? You are a genius. Seriously!

I have no idea why, but the commands won't work all together like that in a program (though exactly the same set of commands pasted together into an SQL box in phpMyadmin works fine). You get a syntax error - the usual mysql useless no information error. But anyway. I split this up into 4 separate commands, and ran them with a program and they worked fine.

Doh.

But thanks!!!

[tiggsy]

Thanks to all that helped. This question is now solved.

[digital-ether]

mysql_query() only supports one query at a time.

note: you can grant permissions to users via SQL querys on Cpanel hosts, you just need to be doing so under a user with the grant option and also the privileges you are granting.
http://dev.mysql.com/doc/refman/4.1/en/grant.html

[tiggsy]

Well, as I pasted a 4-line query in right at the start, I'm surprised nobody told me that before.

[digital-ether]

It is seldom encountered. I just saw a similar post here a while back and that was the conclusion.

[nileshgr]

@tiggsy, damn !! I thought you had already debugged that !