Create A Script

[welbyobeng]

Hi, I am trying to create a script which checks the "users_theme" table and finds all the user_id's which exist in this table and not the "users" table. Once I have those id's, I want to run a foreach loop to delete all rows with that user id from the database.

I did a script where it checks the "users" table and finds all the user_id's which exist in this table and not the "users_theme" table and add it. But I can't seem to create a script which checks the "users_theme" table and finds all the user_id's which exist in this table and not the "users" table. Once I have those id's, I want to run a foreach loop to delete all rows with that user id from the database.

$query="select * from users";
$rs=mysql_query($query)or die("error in sql query".mysql_error());
while($row=mysql_fetch_array($rs,MYSQL_BOTH))
{
$query_add="select * from users_theme where user_id=".$row;
$rs_add=mysql_query($query_add)or die("error in sql query.".mysql_error());
$row_num=mysql_num_rows($rs_add);
if($row_num==0):
$query_ins="insert into users_theme(user_id)values('".$row."')";
$rs_ins=mysql_query($query_ins)or die("error in sql query.".mysql_error());
endif;
}
?>

[ramseswar]

just explain more wat u are tryin to do ...
i cant get it .......if you can do it with users table then wt is the problem with users_theme table.............
and what do you mean by user_id's ???

I'm a little rusty on my MySql syntax since I made my PHP DB Abstraction Class ... but here goes.

<?php
$result = mysql_query( "SELECT DISTINCT `user_id` FROM `users`" );
while ( $obj = mysql_fetch_object( $result ) ) {
  $active[] = $obj->user_id;
}
unlink( $result, obj );

$result = mysql_query( "SELECT `user_id`,`theme_id` FROM `user_theme`" );
while ( $obj = mysql_fetch_object( $result ) ) {
  if ( !in_array( $obj->user_id, $active ) ) {
    mysql_query( "DELETE FROM `user_theme` WHERE theme_id='$obj->theme_id'" );
  }
}
?>

You might have to fiddle with my query syntax, but this basic thing should work...

[welbyobeng]

I got this error when I was trying to run

Warning: unlink() expects parameter 1 to be string, resource given in /..../..../..../..../public_html/test.php on line 15

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /..../..../..../..../public_html/test.php on line 18

[welbyobeng]

for example In simple terms if user_id: 2546 exist in "users_theme" table but does not exist in "users" table delete the row of 2546.

Hi again,

I understand exactly what you're trying to do here...I made a type-o error.

unlink( $result, obj );

should be

unlink( $result, $obj );

in my above code ... You actually probably don't need to do any unlink here, I was just doing some housekeeping before re-initializing those same variables.

Also I made the assumption that you had a field called `theme_id` in your themes table, but you will have to change the value to match the unique key field in that table.

I just tossed the above out there as an example, you should actually look it over and test-debug it for your specific environment.

Enjoy

[welbyobeng]

Hi again,

I understand exactly what you're trying to do here...I made a type-o error.

unlink( $result, obj );

should be

unlink( $result, $obj );

in my above code ... You actually probably don't need to do any unlink here, I was just doing some housekeeping before re-initializing those same variables.

Also I made the assumption that you had a field called `theme_id` in your themes table, but you will have to change the value to match the unique key field in that table.

I just tossed the above out there as an example, you should actually look it over and test-debug it for your specific environment.

Enjoy

Lets Try re-doing the file because I got an error again. It could be a lot easier this way.

require('mysql.php');
require_once('classes/db.class.php');

$db = new db($mysql, $mysql, $mysql, $mysql);

$fetch = $db->query( "SELECT DISTINCT id FROM users" );

while ( $r = $db->fetch_row($fetch) )
{
// insert code here to delete from each table if the user isn't found in the users table
}

Lets Try re-doing the file because I got an error again. It could be a lot easier this way.

Is that a question or a statement ... :-)

[welbyobeng]

Is that a question or a statement ... :-)

O no I am asking for your help to insert the code there to delete from each table if the user isn't found in the users table

Okay,

First realize that I don't have these scripts
require('mysql.php');
require_once('classes/db.class.php');
and I don't actually have the time today to learn them -- so you will have to know how they work to do this.

What you are trying to do has two steps.
1) Get all of the active user id's from the user table.
2) Test every user id in the theme table to see if it exists in the user table, and if not, delete it.

In my first post I created an array of all active users (Step 1) $active[] = $obj->user_id; Then did a select from the theme table and, for each record, tested to see if the user of that theme was in the active users array. (Step 2a) if ( !in_array( $obj->user_id, $active ) ) If not I did a delete action on that record. (Step 2b) mysql_query( "DELETE FROM `user_theme` WHERE theme_id='$obj->theme_id'" ); That's all there is to it.

Since I don't know what methods are available in this class, you will have to evaluate my first code submitted to see where the actions are the same.

$db = new db($mysql['host'], $mysql['user'], $mysql['pass'], $mysql['database']);

$fetch = $db->query( "SELECT DISTINCT id FROM users" );

while ( $r = $db->fetch_row($fetch) )
{
// insert code here to delete from each table if the user isn't found in the users table
}

If you're still stuck then post the error you got from my first code here and I can maybe help you figure that out (?).

Enjoy

[welbyobeng]

Ok this is the error I got for the full code below

Warning: unlink() expects parameter 1 to be string, resource given in /..../..../..../..../public_html/test.php on line 15

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /..../..../..../..../public_html/test.php on line 18

<?
///add entry

$hostname="localhost";
$username="test";
$password="test";
$db="test";
$link_str=mysql_connect ($hostname, $username,$password) or die ('I cannot connect to the database because: ' . mysql_error());
$select_db=mysql_select_db ($db,$link_str)or die('Unable to connect with the database.' . mysql_error());

$result = mysql_query( "SELECT DISTINCT `id` FROM `users`" );
while ( $obj = mysql_fetch_object( $result ) ) {
$active[] = $obj->user_id;
}
unlink( $result, $obj );

$result = mysql_query( "SELECT `id`,`user_id` FROM `users_theme`" );
while ( $obj = mysql_fetch_object( $result ) ) {
if ( !in_array( $obj->user_id, $active ) ) {
mysql_query( "DELETE FROM `users_theme` WHERE user_id='$obj->user_id'" );
}
}

?>

Ooops, I'm a dumb@$$

Just remove that entire line

unlink( $result, $obj );

That is trying to 'unlink' a file...what I meant to write is 'unset'

unset( $result, $obj );

Which destroys the variables and values or those variables...

What was I thinking.

Anyway, you don't need that line at all. It was an attempt at cleaning up after myself before reusing the same variable names.

Try it now.

[welbyobeng]

I got this error after this full code below

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/kmedianet/domains/14kmedia.net/public_html/test.php on line 17

<?
///add entry

$hostname="localhost";
$username="test";
$password="test";
$db="test";
$link_str=mysql_connect ($hostname, $username,$password) or die ('I cannot connect to the database because: ' . mysql_error());
$select_db=mysql_select_db ($db,$link_str)or die('Unable to connect with the database.' . mysql_error());

$result = mysql_query( "SELECT DISTINCT `id` FROM `users`" );
while ( $obj = mysql_fetch_object( $result ) ) {
$active[] = $obj->user_id;
}

$result = mysql_query( "SELECT `id`,`user_id` FROM `users_theme`" );
while ( $obj = mysql_fetch_object( $result ) ) {
if ( !in_array( $obj->user_id, $active ) ) {
mysql_query( "DELETE FROM `users_theme` WHERE user_id='$obj->user_id'" );
}
}

?>

Look here for all the PHP mysql methods, examples and discussions.
http://us.php.net/manual/en/book.mysql.php

I'll try and take a look at this when I get time.

Good luck

Okay, check your syntax against the table-field names and make sure you are querying actual tables and fields in your database.

Example: `users_theme` might actually be `user_theme` named in your `test` database.

Run this to make sure you have the tables defined correctly

$result = mysql_query( "SHOW TABLES" );
while( $row = mysql_fetch_row( $result ) ) {
  print_r( $row );
}

$result = mysql_query( "DESCRIBE `user`" );
while ( $row= mysql_fetch_row( $result ) ) {
  print_r( $row );
}

$result = mysql_query( "DESCRIBE `users_theme`" );
while ( $row= mysql_fetch_row( $result ) ) {
  print_r( $row );
}

Maybe make sure you're getting rows back before running the full match

$result = mysql_query( "SELECT `id`,`user_id` FROM `users_theme`" );
if ( mysql_num_rows( $result ) ) {
  while ( $obj = mysql_fetch_object( $result ) ) {
    if ( !in_array( $obj->user_id, $active ) ) {
      mysql_query( "DELETE FROM `users_theme` WHERE user_id='$obj->user_id'" );
    }
  }
}

Maybe make sure your $active array isn't empty before running the other query

if ( empty( $active ) ) {
  die( "Don't have any users today (?)" );
}

$result = mysql_query( "SELECT `id`,`user_id` FROM `users_theme`" );
// ... more code follows ...

Let me know what you find out...