new to mysql question php

Question

Hello, I have a table in my database that looks like this:

|id|team_name|team_url|xml|

I have a cronjob that calls a script. In this script, I want to use my class to check if the url exists, and if it doesn't, delete the entry in the database. Something like this:

foreach row in table, if (Security::checkUrl(team_url)), delete entry. else: update xml.

How can I do something like this? I don't need help with the url verification only the mysql query and how i should go through each row and delete the rows where the url is invalid.

Thanks.

Answer 1

The mysql query to delete the row would be

DELETE FROM tablename WHERE team_url = '$team_url';

$team_url is the php variable which has the team_url value.

The above command will delete all rows where the team_url matches $team_url.

What you will want to do is in php loop through all the rows and check their URL.

$query = "SELECT * FROM tablename";

// Perform Query
$result = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
while ($row = mysql_fetch_assoc($result)) {
   if (Security::checkUrl($row['team_url'])) {
      $res = mysql_query("DELETE FROM tablename WHERE team_url = '".mysql_real_escape_string($row['team_url'])."'");
   }
   else {
      //update xml
   }
}

mysql_free_result($result);

The above code is just a sample and not to be used in production without proper sql injection cleaning / checking.

Answer 2

To select:

SELECT id,team_url FROM teams ORDER BY id

Now as you work through:

foreach ($row /*...*/) {
  if (/* doesn't exist*/) {
    //Mysql command: "DELETE FROM teams WHERE id=".$row["id"]." LIMIT 1"
  }
}

Answer 3

To delete a row with a given URL, prepare a query like 'DELETE FROM table WHERE team_url=?' with, e.g., mysqli_stmt::prepare(). Then bind the URL that you want to delete to the parameter with mysqli_stmt::bind_param(), à la bind_param("s", $dead_url). Then execute the statement using mysqli_stmt::execute().

EDIT: per strager's suggestion: the mysqli reference in the PHP manual is here: http://php.net/manual/en/book.mysqli.php. It has links to documentation for all the functions that I just mentioned.