Finding the number of rows of each user in MYSQL

Question

I have databases of users and each has tables. I want to loop through each user and find the number of rows of a particular table common to each. So i connect to the first DB(usersDB) and pick the names of other DB's from a table(userinfo) row(user_name). I then connect to each DB using the names obtained in userinfo and try to find the number of rows they each have on a particular table(products) common to them. I tried this but shows the same number of rows for all of them. Any help??

<?php
  //db parameters
 $dbhost = "localhost";   
 $dbname = "usersDB";
$dbuser = "root";   
$dbpass = ""; 

mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());  
mysql_select_db($dbname) or die("MySQL Error: " . mysql_error());  


 //select main db
 $query  = "SELECT user_name FROM userinfo";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_BOTH))
  {
$dbName =$row['user_name'];
  mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());  
  mysql_select_db("dbprefix_".$bName) or die("MySQL Error: " . mysql_error()); 

// do a query for each db
    $query = mysql_query('SELECT * FROM `products`');        
    $num_rows = mysql_num_rows($query);
   echo $dbName." has".$num_rows."products"."<br/>";
} 
  ?>

Have you broken it down to check the variables are what you think they should be? Also what number are you getting out, do you know if it is the first db row count 1 or 0? — BenWells
– BenWells, Commented May 31, 2011 at 15:47
@BenWells: Yes It counts 1. Anyway the problem was that i was not closing the connections. — karto
– karto, Commented May 31, 2011 at 16:23

Tapos · Accepted Answer · 2011-05-31 15:46:23Z

3

I think problem is in following line

mysql_select_db("dbprefix_".$bName) or die("MySQL Error: " . mysql_error());

I think this line will be

mysql_select_db("dbprefix_".$dbName) or die("MySQL Error: " . mysql_error());

answered May 31, 2011 at 15:46

Tapos

6014 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

karto Over a year ago

thanks for the corrections. It was a combination of mysql_select_db("dbprefix_".$bName) or die("MySQL Error: " . mysql_error()); and mySQL Close ();

sadmicrowave · Accepted Answer · 2011-05-31 19:03:11Z

2

this may not be your issue but I noticed you arn't closing the connection to each database after you query from it. you should assign a variable to mysql_select_db and after you echo close the database like this:

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());  
while($row = mysql_fetch_array($result, MYSQL_BOTH)){
   $dbName =$row['user_name'];
   $db = mysql_select_db("dbprefix_".$dbName, $conn) or die("MySQL Error: " . mysql_error()); 
    if( $db ){
        // do a query for each db
        $query = mysql_query('SELECT * FROM `products`');        
        $num_rows = mysql_num_rows($query);
        echo $dbName." has".$num_rows."products"."<br/>";

        mysql_close( $db );
    }
}

also notice I took the mysql_connect() line out of the while loop because you don't need to call this more than once. and I added the $conn variable for your mysql_connect() command, this way you can use $conn in your mysql_select_db() statement. This tell the select_db statement which connection to look in for this database (just alittle more secure).

edited May 31, 2011 at 19:03

answered May 31, 2011 at 15:52

sadmicrowave

41k34 gold badges116 silver badges184 bronze badges

3 Comments

karto Over a year ago

Thanks. It was due to synthax errors and not closing the DB mysql_Close (). It worked. I want to escape breaking execution when there's no database. How can I achieve it?

sadmicrowave Over a year ago

@karto - I edited my post to reflect what you want to do. Basically by checking if $db = true we are asking if $db is a valid database connection otherwise it will be false and nothing will happen.

sadmicrowave Over a year ago

I also edited my answer to include the syntax error you and the other answerers are referring to.

tofutim · Accepted Answer · 2011-05-31 15:47:15Z

2

Seems that there is a typo here:

 mysql_select_db("dbprefix_".$bName) or die("MySQL Error: " . mysql_error());

Did you mean "dbprefix_".$dbName instead of $bName?

answered May 31, 2011 at 15:47

tofutim

23.6k20 gold badges91 silver badges159 bronze badges

1 Comment

karto Over a year ago

Thanks for the correction. Solved now. it was non-closure of my connections.

markpsmith · Accepted Answer · 2011-05-31 15:49:25Z

1

You don't need to call

mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());

every time, just call mysql_select_db for each database and PHP will reuse the connection

answered May 31, 2011 at 15:49

markpsmith

4,9182 gold badges38 silver badges65 bronze badges

Comments

Khurram Ijaz · Accepted Answer · 2011-05-31 15:48:33Z

0

This is a copy paste from php manual may be it helps you

If you use implode() with the return value by mysql_fetch_array,

if you use MYSQL_BOTH on parameter 2, the result is not really what you're expecting.

For example : my sql database contains "Amine, Sarah, Mohamed";

$array = mysql_fetch_array($resource,MYSQL_BOTH); 
or $array = mysql_fetch_array($resource); 
echo implode(" - ", $array);

the result is : Amine-Amine-Sarah-Sarah-Mohamed-Mohamed and we expect just : Amine-Sarah-Mohamed

You must use MYSQL_NUM or MYSQL_ASSOC on parameter 2 to resolve the problem.

answered May 31, 2011 at 15:48

Khurram Ijaz

1,8645 gold badges24 silver badges43 bronze badges

1 Comment

karto Over a year ago

@G molvi: thanks . its worked. MYSQL_ASSOC was'nt the problem, but i had some synthax errors and did not close my connections.

Marc B · Accepted Answer · 2011-05-31 15:55:13Z

0

Seems rather inefficient to start up a new connection, using the same user/password for every user you've got. MySQL is perfectly capable of querying across different databases from the same connection:

mysql_connect(...);
$sql = "SELECT user_name FROM userinfo";
$result = mysql_query($sql) or die(mysql_error()) {

while($row = mysql_fetch_assoc($result)) {
   $username = $row['user_name'];
   $sql2 = "SELECT count(*) AS cnt FROM dbprefix_{$username}.products";
   $result2 = mysql_query($sql2) or (die(mysql_error());
   echo "$username has {$result2['cnt']} products";
}

In short, doing

SELECT somedb.sometable.somefield

is the same as doing

mysql_select_db('somedb');
SELECT sometable.somefield;

answered May 31, 2011 at 15:55

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

Finding the number of rows of each user in MYSQL

6 Answers 6

1 Comment

3 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

3 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related