help needed with small PHP script.

bumbledore

I have a script that is used as a story archive where authors log in and post a story. All data is in MySQL tables. Every so often an author will register and not write a story. When the author numbers get high the script below removes authors with no stories oin their account. But, some people log in and make an account to log their favourite stories or authors. The script removes these authors as well. I would like these authors with fav stories and/or authors to stay. What needs to be added to the script.

favauth, contains uid (userid) and favuid
Favstory, contains uid and sid (story id)

Script:

<?

//Connect to the database, with the right username/password, etc
mysql_connect(localhost, pass, pass) or die("Could not connect to the database");
mysql_select_db("database") or die("Could not select the specified database");

$query = mysql_query("SELECT uid FROM authors");

while($result = mysql_fetch_array($query))

{
$query2 = mysql_query("SELECT sid FROM stories WHERE uid = '$result[uid]'");

$result2 = mysql_fetch_array($query2);

if($result2[sid] == "")

{
$query3 = mysql_query("DELETE FROM authors WHERE uid = '$result[uid]'");

}

}
?>

Thanks in advance.🙂

drawmack

favauth, contains uid (userid) and favuid
Favstory, contains uid and sid (story id)

<?

//Connect to the database, with the right username/password, etc
mysql_connect(localhost, pass, pass) or die("Could not connect to the database");
mysql_select_db("database") or die("Could not select the specified database");

//set up an array to associate with each table
$authors = array();
$stories = array();
$favauth = array();
$Favstory = array();

//setup an array of table names
$tables = array('authors','stories','favauth','Favstory');

//get the uids used in each table
foreach($tables as $cur_table) {
    $sql = "SELECT DISTINCT(uid) FROM $table";
    $query = mysql_query($sql);
    while(($row = mysql_fetch_object($query)) !== FALSE)
        $$cur_table[] = $row->uid;
} //end foreach

//create an array of authors that are have no entries in the other tables
$boneBags = array_diff($authors,$stories,$favauth,$Favstory);

$sql = "DELETE FROM authors WHERE uid in (" . implode(',',$boneBags) . ')';
mysql_query($sql);
?>

I didn't set up a test environment on my server so this untested and probably needs debugging.

bumbledore

Thankyou. I will give it a try and see how it goes.

yelvington

Even simpler: Leave the author accounts alone.

Even if you have hundreds of thousands of registered users, it's not a problem for the database to store the data or look up results.

bumbledore

No, but it is a problem for the user who clicks the author button and goes through 300 authors before finding one who has written a story. That is the problem.

yelvington

SELECT ... WHERE.

bumbledore

I am getting the following error using the code above.

Fatal error: Cannot use [] for reading in path/to/authordelete.php on line 21

Line 21: $$cur_table[] = $row->uid;

Any help appreciated.

tsinka

Hi,

replace
$$cur_table[]
by
$cur_table[]

Thomas

bumbledore

Thanks, I`ll give it a go.

The following error messages appear when run.

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result in path/to/authordelete.php on line 20

Fatal error: [] operator not supported for strings in path/to/authordelete.php on line 21

tsinka

I misunderstood drwamack's code ...

replace
$$cur_table[]
by
${$cur_table}[]

and

$sql = "SELECT DISTINCT(uid) FROM $table";
by
$sql = "SELECT DISTINCT(uid) FROM $cur_table";

tsinka

I'm not sure but a query like the following might give these uids, too:

SELECT a.uid 
FROM authors a LEFT JOIN stories s
ON a.uid = s.uid LEFT JOIN favauth fa
ON s.uid = fa.uid LEFT JOIN Favstory fs
ON fa.uid = fs.uid
WHERE s.uid IS NULL
AND fa.uid IS NULL
AND fs.uid IS NULL

I don't know which solution is faster ... a query or the code above.
I didn't test it. Depends on the indexes ... there should be an index on the uid field in every table.

Thomas

Weedpacket

Originally posted by tsinka
I don't know which solution is faster ... a query or the code above.

Assuming equivalence (and suitable indexes) the query ought to be faster: filtering then transferring the results should be faster than transferring everything then filtering. The filtering has to be done at some stage, so it might as well be done when there are some indexes available to speed the search; and the desired results have to be transferred at some stage also, but it would be nice if you didn't have to transfer unwanted stuff as well.

It actually looks like, however, that you could just replace the "SELECT a.uid FROM authors" in tsinka's query with "DELETE FROM authors". But I guess there will be some code being added in later that will take user input as well.

bumbledore

Hmmmm, the altered code has no effect at all..

Is the last line correct, looks odd with' and"?

tsinka

How does the code look like now ?

Thomas

bumbledore

last line of code.

(" . implode(',',$boneBags) . ')';
mysql_query($sql);
?>

tsinka

Just to test ... what do you get with these lines

echo $sql."<br>\n";
mysql_query($sql) or die("SQL error: ".mysql_error());

Thomas

bumbledore

This is the current code...

    <?

//Connect to the database, with the right username/password, etc
mysql_connect(localhost, pass, pass) or die("Could not connect to the database");
mysql_select_db("Archive") or die("Could not select the specified database");



//set up an array to associate with each table
$authors = array();
$stories = array();
$favauth = array();
$favstory = array();

//setup an array of table names
$tables = array('authors','stories','favauth','favstory');

//get the uids used in each table
foreach($tables as $cur_table) {
    $sql = "SELECT DISTINCT(uid) FROM $cur_table"
    $query = mysql_query($sql);
    while(($row = mysql_fetch_object($query)) !== FALSE)
        ${$cur_table}[]  = $row->uid;
} //end foreach

//create an array of authors that are have no entries in the other tables
$boneBags = array_diff($authors,$stories,$favauth,$favstory);

mysql_query("DELETE FROM fanfiction_authors WHERE uid in (' . implode(',',$boneBags) . ')");

?>

I am getting a blank screen and no authors are removed.

tsinka

Ok,

just for debugging add die to each mysql_query(...) line to check if something is wrong with the queries like e.g.

$query = mysql_query($sql) or die("SQL error: ".mysql_error());

How does the insert query look like if you add a

echo $sql;

line to the code ?

Thomas

bumbledore

I have decided to use the original code that deleted all authors whether they had a favourite story or author listed or not.

<?
 //Connect to the database, with the right username/password, etc
    mysql_connect(localhost, pass, pass) or die("Could not connect to the database");
    mysql_select_db("title") or die("Could not select the specified database");


$query = mysql_query("SELECT uid FROM authors");

while($result = mysql_fetch_array($query))

{

$query2 = mysql_query("SELECT uid FROM favstor");

$result2 = mysql_fetch_array($query2);

$query3 = mysql_query("SELECT uid FROM favauth");

$result3 = mysql_fetch_array($query3);

$query5 = mysql_query("SELECT sid FROM stories WHERE uid = '$result[uid]'");

$result5 = mysql_fetch_array($query5);

//help needed here//
if($result5[sid] == "") and does not have a favstory or a favauthor

{
$query6 = mysql_query("DELETE FROM fanfiction_authors WHERE uid = '$result[uid]'");

}

}
?>

I think I have made the correct alterations, but I am having trouble with the line marked.

if($result5[sid] == "")

will allow ALL authors without a story to be deleted. But I only want authors with no story, no favourite author or no favourite story to be deleted. So an author can not have a story in the archive, but if they have a list of fav authors or stories, they will not be deleted.

tsinka

Hi,

try the following code ... btw ... did you try the left join query I posted ?

<? 
 //Connect to the database, with the right username/password, etc 
    mysql_connect(localhost, pass, pass) or die("Could not connect to the database"); 
    mysql_select_db("title") or die("Could not select the specified database"); 


$query = mysql_query("SELECT uid FROM authors"); 

while($result = mysql_fetch_array($query)) { 
  $delete = 0;
  $uid = $result["uid"];

  $query2 = mysql_query("SELECT uid FROM favstor WHERE uid='$uid'"); 

  if (mysql_num_rows($query2) == 0)
    $delete += 1;

  $query3 = mysql_query("SELECT uid FROM favauth WHERE uid='$uid'"); 

  if (mysql_num_rows($query3) == 0)
    $delete += 1;

  $query5 = mysql_query("SELECT sid FROM stories WHERE uid = '$uid'"); 

  if (mysql_num_row($query5) == 0)
    $delete += 1;   

  if ($delete == 3) { 
    $query3 = mysql_query("DELETE FROM fanfiction_authors WHERE uid = '$uid'"); 
  } 
} 
?>

I'd suggest to use e.g. COUNT(uid) instead of the field itself. That would count the number of records with the given uid and return exactly one record in the record set. Then check if the count is 0.

Thomas