Jump to content


Photo

Help needed echoing a SQL statement !


  • Please log in to reply
9 replies to this topic

#1 cheadirl

cheadirl
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 14 July 2006 - 02:15 PM

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 ?

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


#2 Guest_huey4657_*

Guest_huey4657_*
  • Guests

Posted 14 July 2006 - 02:47 PM

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

#3 CheesierAngel

CheesierAngel
  • Members
  • PipPipPip
  • Advanced Member
  • 105 posts
  • LocationBelgium

Posted 14 July 2006 - 02:50 PM

You want echo your query :

<?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;
?>

You want to echo the count:
<?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;
?>



#4 Oldiesmann

Oldiesmann
  • Members
  • PipPipPip
  • Advanced Member
  • 72 posts
  • LocationCincinnati, Ohio

Posted 14 July 2006 - 03:07 PM

Here's a slightly simpler version:

<?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);
?>

The Oldiesmann
SMF Project Manager
SMF+Gallery2 - Beta3.1 now available

#5 cheadirl

cheadirl
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 14 July 2006 - 03:27 PM

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

#6 Guest_huey4657_*

Guest_huey4657_*
  • Guests

Posted 14 July 2006 - 03:36 PM

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

#7 cheadirl

cheadirl
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 14 July 2006 - 03:39 PM

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 :(

#8 Guest_huey4657_*

Guest_huey4657_*
  • Guests

Posted 14 July 2006 - 03:50 PM

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

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

#9 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 14 July 2006 - 03:56 PM

In simplest terms, here is what you need to do to get results from a mysql DB:
<?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>';
}?>

Replace with your more complicated query ...

Ken

#10 Guest_huey4657_*

Guest_huey4657_*
  • Guests

Posted 14 July 2006 - 03:57 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users