Jump to content

Archived

This topic is now archived and is closed to further replies.

cheadirl

Help needed echoing a SQL statement !

Recommended Posts

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]

Share this post


Link to post
Share on other sites
Guest huey4657
Try $Variable = Your SELECT statement
echo "$Variable";

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Guest huey4657
This may or may not help but have a look at this posting:- PHP Help > Check for duplicates in a MySQL database

Share this post


Link to post
Share on other sites
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 :(

Share this post


Link to post
Share on other sites
Guest huey4657
To display your results you must put the results into a variable and then echo the variable.

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.