Help needed echoing a SQL statement !

[cheadirl]

Hi ,

Ive got 2 tables in my db member and account - I want to be able to run a count to get me the ammount of times a member name matches in both tables, the SQL statement below is doing just that for me the problem I have is I have no idea how to echo that out on a website using php - can anyone help me ?

[code]SELECT COUNT( member.Name ) AS counter, account.Name
FROM member
INNER JOIN account act ON act.Name = member.Name
GROUP BY member.Name
[/code]

[Guest huey4657]

Try $Variable = Your SELECT statement
echo "$Variable";

[CheesierAngel]

You want echo your query :

[code]
<?php
$query = <<<SQL
    SELECT COUNT( member.Name ) AS counter, account.Name
    FROM member
    INNER JOIN account act ON act.Name = member.Name
    GROUP BY member.Name
SQL;
  echo $query;
?>
[/code]

You want to echo the count:
[code]
<?php
$query = <<<SQL
  SELECT COUNT( member.Name ) AS counter, account.Name
  FROM member
  INNER JOIN account act ON act.Name = member.Name
  GROUP BY member.Name
SQL;

$db = mysql_connect(HOST, USER, PASS)or die("Can't connect - " . mysql_error());
mysql_select_db(DBNAME, $db)or die("Can't select db - " . mysql_error());

$results = mysql_query($query, $db)or die("Can't execute query: $query - " . mysql_error());
$result = mysql_fetch_object($results);
echo $result->counter;
?>

[/code]

[Oldiesmann]

Here's a slightly simpler version:

[code]<?php
// Connect to the database server...
$connect = mysql_connect('localhost', 'username', 'password') or die("Could not connect to server!");

// Tell MySQL which database we're working with
$db = mysql_select_db('database', $connect) or die("Couldn't select database!");

// Execute the query
$query = mysql_query("
SELECT COUNT(member.Name) AS counter, account.Name
FROM member
INNER JOIN account act ON act.Name = member.Name
GROUP BY member.Name
") or die("Error executing query! MySQL said: " . mysql_error());

// Display the result
echo 'Result: ' . mysql_result($query, 0);
?>[/code]

[cheadirl]

Hi ,

Ive tried all these methods and Im not gettin any results unfortuantly - I either get Resource id #4 or only a single number printed - I need to be able to print the name and the ammount of times it appears, using the sql statment in SQL server I get

Name | Count
John      10
Anne       4

etc no joy as of yet have tried all the usual echo statements but I just cant figure this one out :(

Any more help would be great thanks

[Guest huey4657]

This may or may not help but have a look at this posting:- PHP Help > Check for duplicates in a MySQL database

[cheadirl]

Thanks for this - Ive seen that post it contains the same DB info that I was looking to generate but no php code to actually display the results on a page :(

[Guest huey4657]

To display your results you must put the results into a variable and then echo the variable.

$VariableName = "Select statement";
echo "$VariableName";

[kenrbnsn]

In simplest terms, here is what you need to do to get results from a mysql DB:
[code]<?php
$q = "select * from tablename";
$rs = mysql_query($q) or die("Problem with query:$q<br>" . mysql_error());
while($rw = mysql_fetch_assoc($rs)) {
  echo '<pre>' . print_r($rw,true) . '</pre>';
}?>[/code]

Replace with your more complicated query ...

Ken

[Guest huey4657]

If there are many names and counts then the output needs to be in a loop in which you extract from the array