|
PHP - Mysql Commands In Php From The Server/cron
Always humbling to be knocked on your arse by what you think would be so easy... How can I run a std mysql_query command like this from cron? $query_insert_new = mysql_query("INSERT INTO members (ID, name, date)VALUES (NULL,'$new_name',CURDATE())"); It is a long time since I firstt learnt how to access mysql from the command line via pUTTY or cmd... However I never learnt how to write a php script in the same fashion... I have configured cron to run correctly and the script runs. After testing however I was wondering how my database was not getting updated... then i realised I must have to enter the commands as if command line... I tried but don't know where to start. Do I have to connect to the database in the same fashion you do in cli: mysql -u username -p password ? This is my script. The idea is to cycle every 7 days and write over old files. It is very simple, but all i need. The table has 3 fields, ID name date. <?php $db_username = "username"; $db_password = "password"; $db_hostname = 'localhost'; $db_database = 'databasename'; //first log into the database $db_server = mysql_connect($db_hostname, $db_username, $db_password); //selecting databse mysql_select_db($db_database, $db_server); //now get the latest entry in the table 'database_backup_info' $query_latest_entry = mysql_query("SELECT name FROM database_backup_info ORDER BY ID DESC LIMIT 1 "); //if there are results in the table if(mysql_num_rows($query_latest_entry)>0) { //retrieving the info $query_latest_entry_row = mysql_fetch_assoc($query_latest_entry); //now determine the new file number based on the last entry if($query_latest_entry_row['name'] + 1 > 7) { $new_name = $query_latest_entry_row['name'] + 1; } else { $new_name = 1; } //insert the entry into the db $query_insert_new = mysql_query("INSERT INTO members (ID, name, date) VALUES (NULL,'$new_name',CURDATE())"); } //if no results then this will be the first entry else{ $new_name = 1; //insert the entry into the db $query_insert_new = mysql_query("INSERT INTO members (ID, name, date) VALUES (NULL,'$new_name',CURDATE())"); } //creating the file path and name $db_backupFile= dirname(__FILE__).'/sql_files/'.$new_name.'.sql'; //the mysql dump command $command = "mysqldump -u$db_username -p$db_password -h$db_hostname $db_database > $db_backupFile"; //running the mysql_dump command system($command, $result); ?> Similar TutorialsI'm reworking a script to write a file in one directory to write another file in a second directory. This works on my testing machine. My shared host, however, is not taking to some of these This topic has been moved to Miscellaneous. http://www.phpfreaks.com/forums/index.php?topic=333230.0 I have a family site with a member list and a forum that both run on MySql. One of the items in the member list is the birthday. What I like to achieve is that a day or 2 before a member's birthday, a post wil be automaticly inserted in the forum, (a post that contains that this persons birthday is comming up in a few days) without any user input. I like to have this done automaticly because I don't feel like setting up a cron job for every member seperatly. There are just too many members. What I need is a script that will create a cron job or something simular the moment a new member registers and updates his profile and sets his birthday. This isn't exactly an application design question, but rather a system design one.
I am about to install an Inventory Control System inside this store I work in.
The store itself also owns a Linode VPS running Centos 6.4 which hosts our website.
This new Inventory System will come built in with a Microsoft SQL Server, and supposedly it is a SQL Anywhere database, but I'm not too sure what that means.
I need to make this database publicly accessible, but only via the Linode VPS. Surely, setting restrictions is easy enough to address that issue. That isn't my question.
My first idea is to put this server into the DMZ, easy. But it doesn't exactly sound safe. So my next idea was to put a middleman server in the DMZ, this way the Linode can send queries to that middleman server and it will send that data to the SQL Server and back. This is very vaguely described I know, but I don't want to get too much into details, but rather, understand how I can create that middleman server, and what could Install onto it that would allow me to securely process queries?
My first thought was to install a webservice, that accepts an XML/JSON request and returns an XML/JSON response.
Then, I realized directly afterwards that I don't have any experience setting up a webservice like that.
What kind of options are there out there? Ultimately, my question is, should I just put the Server in the DMZ or should I create the middleman, and if so, can someone point me in the right direction as to getting a webservice set up? Edited by Zane, 15 July 2014 - 11:28 PM. I am attempting to use use two tables from the same database as described below. My db connection is made and is good. As part of a larger application I am creating a comments feature. Each row is a separate comment that was left by a user. I've no problem getting each row from the database and printing each out as I want formatted. The second table I am using contains all of the personal info of each member including their username, their displayed name, location and among other things the URL to an image they want associated with their account. Each row in the comments table contains the following id # of the comment, username of the person that left it, the date and of course the body of the message itself. Now I am trying to get the comment to show the image found at the URL in the second table (personal info). So for each row in the comments table I want it to pull the commentor's img_url from the second table. $result = mysql_query("SELECT * FROM page_comments WHERE page_id = '$id'"); if(mysql_num_rows($result)==0) { echo "There are currently no comments."; }else{ while($row = mysql_fetch_array($result)) { echo "<table>"; echo "<tr>"; echo "<td>"; echo "<img sro=\"$img_url\" width=50 height=50>"; echo "</td>"; echo "<td>"; echo "<a href=profile.php?member="; echo $row['commentor']; echo ">"; echo $row['commentor']; echo "</a>"; echo "</td>"; echo "<td>"; echo $row['date']; echo "</td>"; echo "</tr>"; echo "<tr>"; echo "<td cospan=3>"; echo $row['comment']; echo "</td>"; echo "</tr>"; echo "</table>"; echo "<br />"; } } As you can see I've thrown the "$img_url" in there as a placeholder for the time being. I've tried a couple of ideas and while none of them threw any errors it, of course, didn't return a value so the img was broken and the HTML source was just empty. Any and all ideas are greatly appreciated. I have a script that is trying to connect from one server to another and it doesn't seem to let it. Could someone help me please Code: [Select] Can't connect to MySQL server on 'example.com' (4) Hi, When i am trying to run one script on my web server, i get following error Cannot connect to the database using the info you provided Access denied for user 'My_Username'@'localhost' (using password: NO) I filled all info like Host(localhost) Database name(created in cpanel) Username(created in cpanel) Password(i kept blank dont know where to find this) You help will highly appreciate Thanks, Ravindra Hello. I just inherited a game stats system but it's not working properly. I'm gonna post the entire script:
<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$servername = "127.0.0.1";
$username = "";
$password = "";
$dbname = "";
$addresses = Array();
$nicknames = Array();
$servers = Array();
$times = Array();
$find_ip = $_GET["ip"];
$find_name = $_GET["nick"];
if(isset($_GET["format"]))
{
$format = $_GET["format"];
}
else
{
$format = "include";
}
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error)
{
ReturnError("NO_CONNECTION");
}
function ReturnError($error)
{
global $format;
if($format == "mod")
die("!DATA!ERROR:" .$error. "!DATA!");
else
die("ERROR:" . $error);
}
function ReturnData($data)
{
global $format;
if($format == "mod")
die("!DATA!SUCCESS:" .$data. "!DATA!");
else
die("SUCCESS:" .$data);
}
function GetServerIP()
{
if (!empty($_SERVER['HTTP_CLIENT_IP']))
return $_SERVER['HTTP_CLIENT_IP'];
else if (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))
return $_SERVER['HTTP_X_FORWARDED_FOR'];
else
return $_SERVER['REMOTE_ADDR'];
}
function GetTimestamp()
{
$now = DateTime::createFromFormat('U.u', number_format(microtime(true), 6, '.', ''));
$now->format("m-d-Y H:i:s.u");
$local = $now->setTimeZone(new DateTimeZone('Europe/Prague'));
return $local->getTimestamp();
}
function GetFormatedTime()
{
$now = DateTime::createFromFormat('U.u', number_format(microtime(true), 6, '.', ''));
$now->format("m-d-Y H:i:s.u");
$local = $now->setTimeZone(new DateTimeZone('Europe/Prague'));
return $local->format("Y-m-d H:i:s.u");
}
function AddAddress($addr)
{
global $addresses;
if(in_array($addr, $addresses, true))
{
return false;
}
else
{
array_push($addresses, $addr);
return true;
}
}
function AddTime($time)
{
global $times;
if(in_array($time, $times, true))
{
return false;
}
else
{
array_push($times, $time);
return true;
}
}
function AddNickname($nick)
{
global $nicknames;
if(in_array($nick, $nicknames, true))
{
return false;
}
else
{
array_push($nicknames, $nick, true);
return true;
}
}
function AddServer($server)
{
global $servers;
if(in_array($server, $servers, true))
{
return false;
}
else
{
array_push($servers, $server, true);
return true;
}
}
function LookForIPs($ip)
{
global $conn;
$sql = "SELECT * FROM `connections` WHERE `IP` = '".$ip."'";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
while($row = $result->fetch_assoc())
{
AddAddress($row["IP"]);
AddServer($row["serverIP"]);
AddTime(Array($row["datestamp"], $row["datestring"], $row["serverIP"]));
if(AddNickname($row["nickname"]) && $row["nickname"] != "Player" && $row["nickname"] != "A_Edition_V2")
{
LookForNicknames($row["nickname"]);
}
}
return true;
}
else
{
return false;
}
}
function LookForNicknames($nick)
{
global $conn;
$sql = "SELECT * FROM `connections` WHERE `nickname` LIKE '".$nick."'";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
while($row = $result->fetch_assoc())
{
AddNickname($row["nickname"]);
AddServer($row["serverIP"]);
AddTime(Array($row["datestamp"], $row["datestring"], $row["serverIP"]));
if(AddAddress($row["IP"]))
{
LookForIPs($row["IP"]);
}
}
return true;
}
else
{
return false;
}
}
function SortTimes()
{
global $times;
$good = false;
while($good == false)
{
$good = true;
for($i = 0; $i < count($times); $i++)
{
if($i+1 == count($times))
continue;
if($times[$i][0] < $times[$i + 1][0])
{
$tmp = $times[$i];
$times[$i] = $times[$i + 1];
$times[$i+1] = $tmp;
$good = false;
}
}
}
}
function GenerateLastSeenString($fromTime)
{
$time_now = GetTimestamp();
if($time_now < $fromTime)
{
return "N/A";
}
if($fromTime == 0 || $fromTime =="")
{
return "N/A";
}
$ss = $time_now - $fromTime;
$seconds = $ss%60;
$minutes = floor(($ss%3600)/60);
$hours = floor(($ss%86400)/3600);
$days = floor(($ss%2592000)/86400);
$months = floor($ss/2592000);
$str = "";
if($months != 0)
{
if($str != "")
$str = $str . " ";
if($days == 1)
$str = $str . $months . " month";
else
$str = $str . $months . " months";
}
if($days != 0)
{
if($str != "")
$str = $str . " ";
if($days == 1)
$str = $str . $days . " day";
else
$str = $str . $days . " days";
}
if($hours != 0)
{
if($str != "")
$str = $str . " ";
if($hours == 1)
$str = $str . $hours . " hour";
else
$str = $str . $hours . " hours";
}
if($minutes != 0)
{
if($str != "")
$str = $str . " ";
if($minutes == 1)
$str = $str . $minutes . " minute";
else
$str = $str . $minutes . " minutes";
}
if($seconds != 0)
{
if($str != "")
$str = $str . " ";
if($seconds == 1)
$str = $str . $seconds . " second";
else
$str = $str . $seconds . " seconds";
}
return $str. " ago";
}
function ClearResult($arr)
{
for($i = 0; $i < count($arr); $i++)
{
if(gettype($arr[$i]) == "boolean")
{
//echo "Found bool at $i";
array_splice($arr, $i, 1);
}
}
return $arr;
}
function PrintArray($arr)
{
for($i = 0; $i < count($arr); $i++)
{
echo $arr[$i];
if($i + 1 != count($arr))
{
echo ", ";
}
}
}
if($format == "mod")
{
LookForNicknames($find_name);
LookForIPs($find_ip);
$nicknames = ClearResult($nicknames);
$addresses = ClearResult($addresses);
$servers = ClearResult($servers);
$conn->close();
/*if (!filter_var($find_ip, FILTER_VALIDATE_EMAIL) && $find_ip != "")
{
$find_ip = $addresses[0];
}*/
if($find_ip == "")
{
$find_ip = $addresses[0];
}
if($find_ip == "")
{
$find_ip = "0.0.0.0";
}
if(count($nicknames) == 0 && count($addresses) == 0)
{
ReturnError("NOT_FOUND");
}
echo "!DATA!";
echo "Nicknames used: "; PrintArray($nicknames); echo "*-*";
echo "Total IPs used: "; echo count($addresses); echo "*-*";
echo "Location: "; echo file_get_contents("http://127.0.0.1/db/getlocation.php?ip=$find_ip"); echo "*-*";
echo "Connected: "; echo count($times); echo " times*-*";
SortTimes();
if(GetTimestamp() - $times[0][0] < 60 * 60)
echo "Last seen: " . GenerateLastSeenString($times[1][0]) . "*-*";
else
echo "Last seen: " . GenerateLastSeenString($times[0][0]) . "*-*";
echo "First seen: " . GenerateLastSeenString($times[count($times)-1][0]);
echo "!DATA!";
}
else if($format == "debug")
{
LookForNicknames($find_name);
LookForIPs($find_ip);
$nicknames = ClearResult($nicknames);
$addresses = ClearResult($addresses);
$servers = ClearResult($servers);
$conn->close();
/*if (!filter_var($find_ip, FILTER_VALIDATE_EMAIL) && $find_ip != "")
{
$find_ip = $addresses[0];
}*/
if($find_ip == "")
{
$find_ip = $addresses[0];
}
if($find_ip == "")
{
$find_ip = "0.0.0.0";
}
if(count($nicknames) == 0 && count($addresses) == 0)
{
ReturnError("NOT_FOUND");
}
echo "Nicknames used: "; PrintArray($nicknames); echo "<br>";
echo "IPs used: "; PrintArray($addresses); echo "<br>";
echo "Location: "; echo file_get_contents("http://127.0.0.1/db/getlocation.php?ip=$find_ip"); echo "<br>";
echo "Connected: "; echo count($times); echo " times<br>";
SortTimes();
echo "Last seen: " . GenerateLastSeenString($times[0][0]) . "<br>";
echo "First seen: " . GenerateLastSeenString($times[count($times)-1][0]) . "<br>";
echo $find_name;
}
?>
The problem is that for some nicknames it returns a proper output which should look like this:
But sometimes it just returns lots and lots of data even if the nickname I ask for is unique enough:
Here's how the database looks:
I don't know what's wrong with the script because I'm a PHP beginner. If someone could take a look at it and tell me what's the issue I'd appreciate. Edited September 22, 2020 by jevgienijThis topic has been moved to Miscellaneous. http://www.phpfreaks.com/forums/index.php?topic=310661.0 hello I wanna replicate one table in a database test(in localhost-master) to another database table(slave) on remote server(on the internet) in godaddy.com. I was really tried my best to make it work. but it doesn't seems to be working. I red the MySQL documentation as well.
database name- test
master table - attendance -on the localhost(127.0.0.1)
slave table - attendance - on the internet(godaddy 184.x.x.x)
I want to copy data from my attendance table on the localhost to slave table on the internet. Please help me to configure these settings. Thank you
Hello, So I have a weird error where if I fetch more than a certain number of rows from a mysql table, it triggers a 500 Internal Server Error. I am using Apache Web Server (through GoDaddy) and the offending code is below: Code: [Select] set_time_limit(0); $this->Connect(); $Output = array(); $search = "SELECT * FROM <table> WHERE user_id = ?"; if($Statement = $this->MySQLi->prepare($search)){ $Statement->bind_param("i", $UserId); $Statement->execute(); $Statement->bind_result(<result variables>); $count = 0; while($Statement->fetch() && $count++ < 70){ ChromePhp::log(<result variables>); } $Statement->close(); } $this->Disconnect(); ChromePhp::log is a way of dumping things to the Javascript Console in your browser from within a PHP script just as a heads-up. So when I set the stop number as 70, everything is fine. If I try to fetch more than that it triggers a 500 internal server error on Apache Server port 443. I have looked through the error logs and can't figure out the cause but this is almost certainly a server configuration issue? I'd appreciate any feedback, especially anyone familiar with GoDaddy's hosting services Thanks This topic has been moved to Miscellaneous. http://www.phpfreaks.com/forums/index.php?topic=352526.0 This topic has been moved to Third Party PHP Scripts. http://www.phpfreaks.com/forums/index.php?topic=333987.0 So I have a big table of about 30-40 columns. Some variables are quite long strings while others are simple tinyint(1) values.
Input into the code is a value or range of values or something that applies to each of the columns.
I need to find which ones are an applicable match to the user request and then load them into php.
Now I know variations can cause something to run more optimally one way or another but this system will get bogged down with lots of requests and lots of data, so any additional performance I give it now will help me down the road. I am just looking for general concepts to help, not specific code tweaking.
I have heard the typical rule is to query mysql as little as possible and do the hard lifting in php but I am not positive this is right.
Since I have to query anyways, should I do a mysql pull using a complex 'WHERE' to do most of the sorting. ie only get rows where a=1, b>5, c="http://google.com", d="3242342323kj4238237489023ejfjf3jrjf8jeifjdjf" ie long string, etc for all 30+columns?
OR I pull the whole DB and do all the sorting in php...
OR I do all the simple sorting in the MySQL query and some of the more complicated sorting in PHP (or vice versa)
Any ideas would help.
Is there any way to have a field that will update any time its row is updated?
My situation is that I will have multiple devices writing to a database, and in one case the time that is being kept track of is important. And the devices writing to the database are a bit difficult to keep accurate times on, so instead of writing the time from the devices themselves, I was hoping I could just have a field update itself. I know it can be done on INSERT, but I do not want to do an INSERT every time.
Thanks!
Is it possible to simply fetch from the server(mySql) and then dump it online through a PHP page? Is there some link that you can share? Here is one of those purely conceptual questions, which involves no code. I'm trying to create a select query which among other things, allows a user of a website to search other members who fall within a particular age range. I have a column in the table where the members' information are stored which records their ages. Which brings us to the problem. On any given day, a member's age may increase by one year compared to what it was the previous day, hence the need to update this column periodically. I can't think of any way to automatically update this column on a daily basis. So my solution is to run an initial update query every time a member tries to search other members based on age, which updates the age column for all other members, before running the select query which eventually retrieves the desired age range. This leads to the second problem. Imagine there are thousands of users using the website. At any given instance, there could be hundreds of members, trying to search others based on age. This means hundreds of users will be updating a single column (the age column) in one table at the same time. Is this feasible? Can it cause the server to crash? Or is there really a more reasonable way to do all of this? Thank you all for taking your time to read this. Appreciate any responses. I have one mysql table with data and accessing this data through one php script. This works fine in REDHAT 8 linux. I have shifted mysql database and the php script to new server with REDHAT RHEL4.4. now when I execute the script from website, it displays junk character (reading form mysql database through php script). There is no error in mysql database/table and script is working fine in redhat 8. I have checked php.ini for default characterset as well as set the encoding in mysql as latin1. Where else should I look/change? any idea? Good Day
I have a bit of an issue with my website I loaded on my domain.
I am getting an error:
SQLSTATE[HY000] [1130] Host 'xxx.xxx.xx.xxx' is not allowed to connect to this MySQL server
Can some one please explain to me in detail how do fix this?
It only shows on my Sale and Rent pages
|
|||||||