How to create a PHP file

[jsprague]

Hello,

 

Let me preface by saying that I am not a programmer or MySQL expert.

 

I am trying to find a way to create a PHP file that I can set up as a cron job. In this PHP file, I would like to run the following MySQL commands..

 

update inl_links set link_name = replace(link_name,'(Summer seasonal)','<img src="http://www.beertutor.com/images/summer.gif" border="0" alt="Summer seasonal">')

update inl_links set link_name = replace(link_name,'(Winter seasonal)','<img src="http://www.beertutor.com/images/winter.gif" border="0" alt="Winter seasonal">')

update inl_links set link_name = replace(link_name,'(Spring seasonal)','<img src="http://www.beertutor.com/images/spring.gif" border="0" alt="Spring seasonal">')

update inl_links set link_name = replace(link_name,'(Fall seasonal)','<img src="http://www.beertutor.com/images/fall.gif" border="0" alt="Fall seasonal">')

update inl_links set link_name = replace(link_name,'(Special release)','<img src="http://www.beertutor.com/images/special.jpg" border="0" alt="Limited Release">')

 

What is the proper syntax in my PHP file to execute these MySQL commands?

 

Thanks much!!

Jason

[micah1701]

mysql_connect($host, $username, $password);

mysql_ select_ db($database_name); //optional - but if you skip this, you have to name the database in the query

 

$query = "SELECT column FROM table";

 

mysql_query($query) or die(mysql_error());

 

[The Little Guy]

does your host support SQL cron?

 

if so, then you don't need to write it as a PHP file like micah1701 is suggesting, but as an sql file, like you have, just the sql statements and that is it, where each statement ends with a semi colon.

 

Sample Cron:

/usr/bin/mysql -hmysql.tzfiles.com publicsize --user='MyUserName' --password='MyPassword' < /home/ryannaddy/cron/logOut.sql

 

 

Edit:

 

Oh yeah... you would need to save it with the .sql extention

[jsprague]

Thanks much for the info.. I am serious when I say that I don't know anything about this stuff so I don't entirely understand the answers. Can someone show me exactly what the file would look like with my MySQL statements?

 

Thanks again!

Jason

[The Little Guy]

They would look just like your first post, just add a semi-colon to the end of each line, then save it as an SQL file

 

Example:

xxx.sql

 

update inl_links set link_name = replace(link_name,'(Summer seasonal)','<img src="http://www.beertutor.com/images/summer.gif" border="0" alt="Summer seasonal">');

update inl_links set link_name = replace(link_name,'(Winter seasonal)','<img src="http://www.beertutor.com/images/winter.gif" border="0" alt="Winter seasonal">');

update inl_links set link_name = replace(link_name,'(Spring seasonal)','<img src="http://www.beertutor.com/images/spring.gif" border="0" alt="Spring seasonal">');

update inl_links set link_name = replace(link_name,'(Fall seasonal)','<img src="http://www.beertutor.com/images/fall.gif" border="0" alt="Fall seasonal">');

update inl_links set link_name = replace(link_name,'(Special release)','<img src="http://www.beertutor.com/images/special.jpg" border="0" alt="Limited Release">');

[jsprague]

Thanks again for the help.. My host does not support SQL cron, so I have to go the PHP route.. Does this code look correct?

 

<?php

mysql_connect(localhost, my_username, my_password);
mysql_ select_ db(beerdirectory);

$query = "update inl_links set link_name = replace(link_name,'(Summer seasonal)','<img src="http://www.beertutor.com/images/summer.gif" border="0" alt="Summer seasonal">')";

$query = "update inl_links set link_name = replace(link_name,'(Winter seasonal)','<img src="http://www.beertutor.com/images/winter.gif" border="0" alt="Winter seasonal">')";

$query = "update inl_links set link_name = replace(link_name,'(Spring seasonal)','<img src="http://www.beertutor.com/images/spring.gif" border="0" alt="Spring seasonal">')";

$query = "update inl_links set link_name = replace(link_name,'(Fall seasonal)','<img src="http://www.beertutor.com/images/fall.gif" border="0" alt="Fall seasonal">')";

$query = "update inl_links set link_name = replace(link_name,'(Special release)','<img src="http://www.beertutor.com/images/special.jpg" border="0" alt="Limited Release">')";

mysql_query($query) or die(mysql_error());

?>

 

my_username and my_password would be replaced with the appropriate values.

 

Thanks!!!

[micah1701]

sort of but...

 

if you're not using $variables for your host, username, password and database name - make sure you put them in quotes.

 

also, you're going to need to run the mysql_query() function for EACH of your separate query statements.

 

I noticed in your SQL statement that you have some "double quotes" those need to be \"escaped\" because they are inside the double-quotes of the string.

 

also, the "or die(mysql_error() )"  ending to each query is optional, it just helps diagnos a problem if the query fails.  If yours don't work put it back in to see what the problem is...otherwise, you can leave it out..

 

<?php

mysql_connect("localhost", "my_username", "my_password");
mysql_ select_ db("beerdirectory");

mysql_query("update inl_links set link_name = replace(link_name,'(Summer seasonal)','<img src=\"http://www.beertutor.com/images/summer.gif\" border=\"0\" alt=\"Summer seasonal\">') ");

mysql_query("update inl_links set link_name = replace(link_name,'(Winter seasonal)','<img src=\"http://www.beertutor.com/images/winter.gif\" border=\"0\" alt=\"Winter seasonal\">') ");

// ... repeat for each query you're running

?>

 

EDIT: Also, i didn't really look at your query statement before but it looks a little iffy - that might be a whole new thread though ;-)    shouldn't you have a WHERE clause at the end?

[jsprague]

Thanks so much!!! Excellent help

[The Little Guy]

This should work, if you haven't gotten it yet.

 

<?php

$dbHost = "localhost";        //Location Of Database usually its localhost
$dbUser = "xxxx";            //Database User Name
$dbPass = "xxxx";            //Database Password
$dbDatabase = "xxxx";       //Database Name

$db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");
mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database."); 

$query = "update inl_links set link_name = replace(link_name,'(Summer seasonal)','<img src=\"http://www.beertutor.com/images/summer.gif\" border=\"0\" alt=\"Summer seasonal\">')";
mysql_query($query) or die(mysql_error());
$query = "update inl_links set link_name = replace(link_name,'(Winter seasonal)','<img src=\"http://www.beertutor.com/images/winter.gif\" border=\"0\" alt=\"Winter seasonal\">')";
mysql_query($query) or die(mysql_error());
$query = "update inl_links set link_name = replace(link_name,'(Spring seasonal)','<img src=\"http://www.beertutor.com/images/spring.gif\" border=\"0\" alt=\"Spring seasonal\">')";
mysql_query($query) or die(mysql_error());
$query = "update inl_links set link_name = replace(link_name,'(Fall seasonal)','<img src=\"http://www.beertutor.com/images/fall.gif\" border=\"0\" alt=\"Fall seasonal\">')";
mysql_query($query) or die(mysql_error());
$query = "update inl_links set link_name = replace(link_name,'(Special release)','<img src=\"http://www.beertutor.com/images/special.jpg\" border=\"0\" alt=\"Limited Release\">')";
mysql_query($query) or die(mysql_error());

?>