Select count with options

Beginners - Learning PHP
#41

Well, if you can’t make a regular connection, fix it. You need to review your server logs and see what error you are getting at that line. Or add error-checkng to it to show the error. You do need to replace the server name, user name and password to get it to work.

You have a very complicated template your are using. Normally, for a beginner, that makes things very hard and the learning curve is higher. You have to study every page in the template you are using and figure out what each does and understand it well and move onto the next one. And, you do need to know the structure of your database so you know the data that is available for your use. There are programs you can run to review that part or you could log into the server and review the structure.

#42

ErnieAlex, Thanks for that reply, hope you enjoyed the party.
I can’t see server logs because have not got access so that won’t help.
Think the template of these files is built in such a way that is very hard to do changes or add things (at least for me).
Here is what i have tried, added the new connection string gets no access to database with all the same user, password etc.

Have tried the following replaced this

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

with this

if (isset($HostedUrl))
{
$conn = new HostedDataConnection($HostedUrl, $HostedKey);
if ($HostedAdmin)
$AdminConnection = $conn;
}
else
{
$conn = new DataConnection($DatabaseHost, $DatabaseName, $DatabaseAccount, $DatabasePassword);
if (isset($AdminDatabaseHost))
$AdminConnection = new DataConnection($AdminDatabaseHost, $AdminDatabaseName, $AdminDatabaseAccount, $AdminDatabasePassword);
else
$AdminConnection = $conn;
}
Got same error “cannot connect to database”
after that i looked through all the files in the template to find $Connection and replace with $conn as well still same error.

Starting to think that it cannot be solved without writing the whole template again, which i know i won’t be able to do.
But thanks so much for all your help.

#43

Seem to be getting near to the awnser with a few changes in above code after going through all of the files in my template.
Here is what i’m using

$sql = “SELECT , count() AS child_count FROM people GROUP BY Father ORDER BY child_count DESC”;
$result = $conn->RunQuery($sql);
while($record = $result->GetAssocRecord($n) ) {
echo “
” . $record["$_name"] . “
”;
$sql2 = ‘SELECT * FROM people WHERE father=$record["$_name"]’; // doubles inside single quotes
$result2 = $conn->RunQuery($sql2);
while($record2 = $result2->GetAssocRecord($n)) {
echo $record2[“child”] . “
”;
}
}

I will try and explain RunQuery replaces query in all the other template files.
GetAssocRecord($n) replaces fetch_assoc() in all the other template files,
$_name is father in all the other template files.

Runs until line 7 which is the following
$sql2 = ‘SELECT * FROM people WHERE father=$record["$_name"]’; // doubles inside single

I get the following error
Parse error: syntax error, unexpected ‘$_name’ (T_VARIABLE) in bla bla bla.php on line 7
Now trying to work out why the first $_name works but not the second one.
I have tried “$_name” with single quotes still same error
Think i’m finally close to a solution.

#44

I understand the replacements. But, $record and $record2 are ROWS of data. In them, you will have the live fieldnames from the database table. Therefore, $_name is not a valid fieldname as it is a variable. Try in both places to use $record[“name”] and $record2[“name”] and see if that fixes it for you.

Nice! It does seem like you are getting close to a solution !

#45

ErnieAlex, thanks for that reply again,
I tried that
Try in both places to use $record[“name”] and $record2[“name”]
Which actually seemed more logic to me.
Now get another error

==SQL logic error\r\nnear “[“Name”]”\c syntax error
This again is from the same line as above which is
$sql2 = ‘SELECT * FROM people WHERE father=$record2[“Name”]’; // doubles inside single quotes

#48

Can you put your app on Github so we can review it as a whole?

#49

benanamen
Not really sure if that is allowed, it’s not my ap, i am just using the files i was given in a template.
all the files have Copyright in them but i am allowed to modify them.
I understand that that solution would save everyone a lot of time.

#50

Well, most likely the “Name” is not spelled like that. I mean, the father’s name is most likely “father” NOT “Name”.

You would need to know the names of the fields to do this part correctly.

#51

Ok lets try again because i have probably not explained properly what i am trying to do

I have a database table named people
In the table i have three columns which are
Name (which is primary key) varchar(64)
Father varchar(64)
Mother varchar(64)
In those columns there is the following for example
Name/Father/Mother (Names of Columns the / is just in example to show what is in each column)
bob/max/jane
max/martin/janet
martin/bob/marge
billy/bob/jane
jane/martin/janet
janet/billy/jane

From that you can see
Bob has two children
Martin has two children
Billy has one child
I want to get that from the table and show,
Bob 2
Martin 2
Billy 1
etc in a descending order

#52

Well, my code I posted should work. Except, you must fix the field names. So, like “father” must be “Father” etc. Fix the names and it should work for you…

Normally, you don’t use caps in fieldnames. Nor in programming variables. Just too hard to remember which had caps and which do not.

Anyway, fix the field names and it should work.

#53

ErnieAlex
Looking at the other files in the template i have just found this

class FieldIndex
{
const NAME = 0;
const SEX = 1;
const FATHER = 2;
const MOTHER = 3;
Not sure if these are the field names you are saying because in database table they are
Name
Sex
Father
Mother
I am trying again to get it to work with everything suggested, but these files seem to be written in a very strange way and nothing works.

#54

Const’s are used to set defaults for later on. In this case, for example, FATHER is number 2, probably means it was indexed as number 2. ( people[2] is same as people[“Father”] but not used the same as normal indexes. )

In your testing, you should be getting errors and line numbers of where they are. Just fix them one by one.

#55

That is what i am been doing since i started this topic changing it line by line until no errors

This was your code
$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 “
” . $record[“father”] . “
”;
$sql2 = ‘SELECT * FROM people WHERE father=$record[“father”]’; // doubles inside single quotes
$result2 = $conn->query($sql2);
while($record2 = $result2->fetch_assoc()) {
echo $record2[“child”] . “
”;
}
}
That became the following

$sql = “SELECT , count() AS child_count FROM people GROUP BY Name ORDER BY child_count DESC”;
$result = $Connection->RunQuery($sql);
while($record = $result->GetAllAssocRecords() ) {
echo “
” . $record[“Name”] . “
”;
$sql2 = ‘SELECT * FROM people WHERE Name=$record[“Name”]’; // doubles inside single quotes
$result2 = $Connection->RunQuery($sql2);
while($record2 = $result2->GetAllAssocRecords()) {
echo $record2[“child”] . “
”;
}
}

It was fixed line after line until last error which was
while($record2 = $result2->fetch_assoc()) {

which became
while($record2 = $result2->GetAllAssocRecords()) {

This was all logic for me as i went through errors and i thought Name was what to select in the first line because that’s the header of the column in my database table.

Now it takes me back to an error on line 1
“Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘[“Name”]’ at line 1”

#56

Again, the count() is missing the star! count(*) ! ! !

#57

You asked to group by father, not all people. Therefore, you can not GROUP BY Name as that would give you a different result than you asked for.

#58

It’s in there sorry it did not copy over to here properly

$sql = “SELECT , count() AS child_count FROM pedigree GROUP BY Name ORDER BY child_count DESC”;

When i reply it takes the star out of the code but it is in there.

#59

Also, you SELECT comma… Invalid… Was supposed to be SELECT , count() AS child_count

Not sure why you would change that !

#60

Okay, I will explain how to post code here. The absolute easiest way is to just add three tick-marks at the begging and end of code:

This code line has three ``` before the line on a separate line and nother three after
#61

If i take out the Coma i get this error
Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘count(*) AS child_count FROM pedigree GROUP BY Name ORDER BY child_count DESC’ at line 1

#62

you need a , count() as child_count
Both the START and the COUNT(*) AS child_count…

Sponsor our Newsletter | Privacy Policy | Terms of Service