Select count with options

Beginners - Learning PHP
#21

Just use order, count and group by… Something like this:

SELECT *, count(*) AS child_count FROM people GROUP BY Father ORDER BY child_count DESC

This will select everything, group it by father creating a child_count of how many children and sort it by the child_count numbers. You then use WHILE() loop to display each Father plus the child_count results. If you need the names of the children, you need to add in a nested WHILE() loop to display those using a second results from a query.

Well, when you run a query, you return a value. Sometimes this “value” is null or just empty. In PHP, you just compare the value and if it is null or empty, display something else. Are you doing this father-of-father in just one query? If so, show us and I can tell you how to do alternative results in a query. ( You just have to use “IF IS NULL” condition and select the value or the substitute. If you are doing this inside your PHP code, you just compare the results. $row or $results whatever… If ($row[“father”]==NULL) { nothing…} or if (trim($row[“father”])=="") { nothing…}

Hope this helps!

#22

ErnieAlex thanks for the reply tried that as you suggested which is simuler to other things i have tried.
It just results with the following
*Parse error: syntax error, unexpected ‘,’ in http etc on line three"
Not sure if its possible but could there be something in one of the PHP files that prevents adding new queries etc?, for the second question that you suggested what to try i need to look into it more before trying.

#23

What I typed and what you just typed are NOT the same.

SELECT , count() will get all the records which you need for the GROUP-BY and ORDER-BY.

You left out the two stars…

#24

Sorry, posted the wrong phrase, this is because i got confused at first with the stars i got parse error unexpected ‘*’ took one off still the same error, took second one off got parse error unexpected ‘,’
I know its probably not the way to try and stop errors but i’m trying everything.

Only way to stop errors is the following code

<?php $sql = "SELECT * count(*) AS child_count FROM people GROUP BY Father ORDER BY child_count DESC"; $sql = 'while(SELECT child_count FROM people WHERE Father=$results["Father"])'; echo "Name of Father: $results
"; $results++; ?>

which shows on the webpage Name of Father:
so its not doing anything except the echo Name of Father:

#25 
$sql = "SELECT *, count(*) AS child_count FROM people GROUP BY Father ORDER BY child_count DESC";
$result = $this->RawQuery($sql);
while($record = $result->fetch();
    echo "<br>" . $record["father"] . "<br>";
    $sql2 = "SELECT * FROM people WHERE father=$record["father"]";
    $result2 = $this->RawQuery($sql2);
    while($record2 = $result2->fetch();
        echo $record2["child"] . "<br>";
    }
}

Something loosely like this should work. Nested queries and loops.

Hope that works…

#26

Thanks again for all your help but sorry i’m giving up for tonight its past midnight here.
with the above code i get
Parse error: syntax error, unexpected ‘;’ in /http://blablabla.php on line 5

#27

Again, sorry, my fault Typed quickly. You can not have quotes inside of quotes.
Try this whenever…

$sql = "SELECT *, count(*) AS child_count FROM people GROUP BY Father ORDER BY child_count DESC";
$result = $this->RawQuery($sql);
while($record = $result->fetch() ) {
    echo "<br>" . $record["father"] . "<br>";
    $sql2 = 'SELECT * FROM people WHERE father=$record["father"]';  // doubles inside single quotes
    $result2 = $this->RawQuery($sql2);
    while($record2 = $result2->fetch()) {
        echo $record2["child"] . "<br>";
    }
}
#28

Thanks ErnieAlex,
Sorry just tried now getting the same error again but on line four

Parse error : syntax error, unexpected ‘;’ in /http://blablabla.php on line 4

Quotes Again ?

#29

Well, that was only test code so that yhou could get started. I did edit it as the WHILE lines were wrong.
Too many people were here and I was typing off the top of my head. Hope this fixes it.

#30

ErnieAlex, thanks so much for all your replies and the time spent, I understand that it was test code.
Getting a new error now : Fatal error : Uncaught Error: Using $this when not in object context in line 4

I’ve just spent a long time to try and work it out with no success.
Think i need to learn how everything works before trying to add code

#31

Well, I thought that made no sense, but, thought you wrote it. Here is the problem…

$Connection->RunScalar($sql);

This is something you showed us. This means you have a $Connection string. And, in it you have a method called RunScalar and you send the SQL or QUERY to that routine. I have no idea what that is all about. I assumed if you wrote that complicated of an OBJECT that you knew what you were doing and therefore, I assumed that my example would get you moving. But, it appears not. If you can list how you create that connection, it would help us. OR, did you create this from something you found on the web?
We need to know how $Connection routine is set up and what runScalar method does…

Objects and method classes are sometimes hard to learn for beginners. But, keep going…

#32

ErnieAlex, I did not write that “$Connection->RunScalar($sql);” its already a function in a php file.
this is all i know about it

function RunScalar($sql)
{
$result = mysqli_query($this->link, $sql);
if (!$result)
return 0;
$record = mysqli_fetch_row($result);
$result->close();
return $record[0];

and as for the $Connection routine i think it is this from the config.php file

if (isset($HostedUrl))
{
$Connection = new HostedDataConnection($HostedUrl, $HostedKey);
if ($HostedAdmin)
$AdminConnection = $Connection;
}
else
{
$Connection = new DataConnection($DatabaseHost, $DatabaseName, $DatabaseAccount, $DatabasePassword);
if (isset($AdminDatabaseHost))
$AdminConnection = new DataConnection($AdminDatabaseHost, $AdminDatabaseName, $AdminDatabaseAccount, $AdminDatabasePassword);
else
$AdminConnection = $Connection;

I can change the connection method between $HostedUrl an $Connection in the config file with //
I am on the hosted version as i cannot find hosting that have Remote Access (Needed for the program on computer)

And just to explain most of the things i have changed in the PHP files i was given are the echo statements to echo out what i needed.

Hope that can help

#33

Yes, this tells me that you have a “class” set up that calls another function. This above function only gets one record and will not work for your current needs. I appears it is for getting just one record like when you want to get the counts of data. Handy, but, will not get all the data you need for this display.
But, inside that function is another one called “mysqli_fetch_rows” and “mysqli_query” . These are normal MySQLi calles. Therefore we can just use them instead of calling the odd RunScalar function…
Therefore, try this. ( Previous one with these standard functions )

$sql = "SELECT *, count(*) AS child_count FROM people GROUP BY Father ORDER BY child_count DESC";
$result = $Connection->query($sql);
while($record = $result->fetch() ) {
    echo "<br>" . $record["father"] . "<br>";
    $sql2 = 'SELECT * FROM people WHERE father=$record["father"]';  // doubles inside single quotes
    $result2 = $Connecton->query($sql2);
    while($record2 = $result2->fetch()) {
        echo $record2["child"] . "<br>";
    }
}

Now, this is just a guess going from what you posted. The code is a messy way to create a class to be used by a beginner. The class creates objects. These objects are used using the - > operator. So, the $Connection is your base link to the database. From there you use $Connection-> to access methods. In our case to run full queries or get rows of data as needed. Hope that helps explain it somewhat.

Try this version and let me know what happens. Going out of town soon. Will be gone for a few days!

#34

Thanks tried got this error : Fatal error : Uncaught Error: Call to undefined method HostedDataConnection::query()

So if i’m starting to understand means i don’t have a function or a class named “query”
Just changed it to RawQuery now its fetch() yjat doesn’t work

#35

So, you are doing this inside a classroom? I mean you were given some starting things like the connection items and told to use those?

Normally this would not use the $this->link. It would include the connection string instead.
Using $this->link means it is using an OBJECT. You would need to know what that was. It appears that the class was written so it would run locally or on a remote server and would get different abilities depending on where it was run. But, if this is for a class, just show the teacher your code we just tested and ask what the connection string should be. Not sure what else to try.

#36

No not in a Classroom, was given the PHP file package to use for my website,
made some changes to fit in better with my website, echo etc in php and the css files.
My website is actually working great and i’m doing it in other languages. At the moment it’s only in English and French.
The problem comes everytime i need to add something new (Like this subject) i don’t know enough about PHP to do it.

#37

Okay, I understand. Well, the code I last posted is valid. It is just the connection string that is complicated by all the extra code your were given. We could work around this issue by just creating a new connection string. Then, in your areas where you need to change things, you can just use the new connection string instead of your $Connection version. In that manner, you will have two database connections which is not really a problem. Here is how you would do it.

Make a new database connection. This would be done in your config.php file. It would look like this:

// Create connection
$conn = new mysqli($servername, $username, $password);

$conn is commonly used as the database connection string name. We can use that one. Your version, not written by you, uses capital letters which are not used as they are confusing to others reading the code. We will use $conn and use it when needed. In your config.php file, you can add this line, but, you will need to use the three arguments. I think it will look like this:

//  Create second connection
$conn = new mysqli($DatabaseHost, $DatabaseAccount, $DatabasePassword, $DatabaseName);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

Try adding this after these three items have been declared in your config.php file.

Next, we will have to change the code from before like this:

$sql = "SELECT *, count(*) AS child_count FROM people GROUP BY Father ORDER BY child_count DESC";
$result = $conn->query($sql);
while($record = $result->fetch_assoc() ) {
    echo "<br>" . $record["father"] . "<br>";
    $sql2 = 'SELECT * FROM people WHERE father=$record["father"]';  // doubles inside single quotes
    $result2 = $conn->query($sql2);
    while($record2 = $result2->fetch_assoc()) {
        echo $record2["child"] . "<br>";
    }
}

I changed the queries just a little because we are not using the CLASS any more. And, it is hard to say if this will work, but, should. Later if it does not, we should create a test page so all the code can be posted. But, I am leaving the state for 3 days for a birthday party and will not be online. So, will be quiet for awhile. Hope this works for now.

#38

ErnieAlex, thanks for all your help i will try all of that see what happens.
Enjoy yourself where you are going will reply on here in a few days to let you know.

#39

Thanks, it’s a birthday party for me that my buddy is throwing. My birthday was last Sunday.
Catch up with you in a few days…

#40

After a few days of testing, the above solution of creating a new $conn does not work.
I get get the error cannot connect to Database.
I have an idea why but need to find the solution, the config file uses another file called common.
In the common file i can see the following

global $Connection, $szPrivateTest, $szDatabase;
$record = array();
if (count($list))
{
$escaped = array();
foreach ($list as $item)
array_push($escaped, “’” . $Connection->Escape($item) . “’”);
$mateList = implode(’, ', $escaped);
$sql = “select * from $szDatabase where $szPrivateTest name in ($mateList)”;
$result = $Connection->RunQuery($sql);

Maybe that code needs to be changed to add the $conn option as well so that both can be used??
I have no idea i’m just guessing at what could be the solution.
Thanks.

Sponsor our Newsletter | Privacy Policy | Terms of Service