Jump to content

Help needed echoing a SQL statement !


cheadirl

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]
Link to comment
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]
Link to comment
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]
Link to comment
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
Link to comment
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";
Link to comment
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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.