[An INNER JOIN problem.]

You could have check your mysql_error() and let us know yourself...

It seems you are using dynamic variables for columns names in both the column list and ON clause. Those are usually static values - what is the value of $egroup and $managerId? why are those variables? what are the contents of mysql_error()?

[Need help with a very tricky mysql query (one table)]

You'll find it much easier to achieve if you normalize your database. Have a table for topics and a table for posts

 

Something like:

 

topics

  - id

  - title

 

posts

  - id

  - topic_id

  - message

[MySQL PHP queries-syntax not clear]

The dot '.' operator is used to concatenate string in PHP.

http://php.net/manual/en/language.operators.string.php

[mysql error]

You have failing queries. mysql_query() returns a resource or a boolean false - the error is telling you that you trying to pass a failed query result into mysql_num_rows() which expects a mysql result resource.

 

You need to perform a check before passing a mysql_query(), something of the form

 

$result = mysql_query(...);
if($result) {
    //use result in mysql_num_rows or any other relevant function
} else {
    // debug / log error using mysql_error()
}

[database class review]

Writing your own is well and good and does has some learning value, but at least have a look at mature packages that have been developed and tested by many seasoned developers. You can learn a lot from reading the Zend_Db classes as phpfreak suggested

[Whats best?]

Don't index blindly. Understand how indexes work and how you should apply them.

I suggest going over this excellent presentation on indexing strategies - http://www.scribd.com/doc/2376115/Coding-and-Indexing-Strategies-for-Optimal-Performance

[mysql query result]

mysql_query() returns a resource if the query was successful. To get database rows, you need to use fetch function such as mysql_fetch_assoc() on the result

 

$result = mysql_query($query);
i($result) {
    $row = mysql_fetch_assoc($result);
}

 

http://php.net/manual/en/function.mysql-fetch-assoc.php

[PHP/MySQL Search (like Google)]

You should use a document-based text indexing solution such as Lucene or Sphinx. Both are highly performant (especially sphinx) and fit your need much better than MySQL.

[OOP: Bad practice?]

The only issue I have with that is that there is no need at ALL to pass $_POST or even $_GET data, or realistically ANY superglobal into a function,

There is a reason, and that is if you are not always expecting the data to come from a superglobal. If you pass in an array, you can create the same object using a form submission, from inside another object, from database data and so forth. By passing the data as an array instead of relying on it existing in superglobals you are increasing the re-usability of the class.

[SELECT COUNT]

@joel24 I got unknown table a

@eran it gives be a blank value  it would be $info['postCount'] right?

No, it should be $info['posts']. var_dump($info) to see what it contains

[SELECT COUNT]

Left join the ads table

SELECT y.*,COUNT(ads.UID) AS posts FROM YBK_Login AS y
LEFT JOIN YBK_Ads AS ads ON ads.UID=y.ID
GROUP BY y.ID

[Unknown column in 'on clause']

I showed you exactly how in my previous response. You join the first two tables using the implicit syntax (separating them with a comma) and the rest using the explicit (specifying the join type and on clause). I suggested you change the implicit syntax to the explicit syntax and wrote the exact statements to use

[Unknown column in 'on clause']

This is why you shouldn't mix join syntax. Try fenway's suggestion, or change all the joins to explicit joins -

 

Change:

FROM tplss_seasons S, tplss_seasonnames SE

 

To:

FROM tplss_seasons AS S

INNER JOIN tplss_seasonnames AS SE ON SE.SeasonID = S.SeasonID

 

And make sure to remove that condition from the WHERE clause

[MySQL Sessions or Filesystem Sessions]

Sessions in the database or in the filesystem is not much a question of security as thorpe says, but more a question of scalability. For most sites having the sessions in the filesystem is the easiest and best option, however when you grow beyond a single server, sharing filesystem sessions between servers becomes very problematic. In this case sessions are usually moved to a database to which all servers have access to

[LIMIT a GROUP BY query]

GROUP BY Department?

 

You can also use SELECT DISTINCT Department FROM $table

[Database model for CMS hosting multiple sites]

There's no problem in having a lot of rows in one table - that's what tables are for. MySQL can scale to hundreds of millions of table rows and beyond. As long as the tables are normalized - which means that data they store has the same structure for each, and that looks to be the case here - you are doing well.

 

Something to consider though - will one costumer have more than one site? in that case perhaps have an intermediary 'sites' table that maps clients to sites and sites to content

[Get amount of each unique...?]

Two things - as for your problem with the unique IPs, I suggest you keep a counter for each project and update it every day with a cron job (for the last day's unique hits). It might be possible to do it with a query on demand, but it will be a very heavy duty query and that's probably not worth it.

Regarding your approach to calculate daily hits, you can achieve the same effect using one query for each range. Something like the following:

 

SELECT COUNT(DISTINCT(ip)) as unique_hits, COUNT(ip) as total_hits, '".$xdate."' as raw_date,
date_format(date,'%Y-%m-%d') as date_only 
date_format('".$xdate."','%m-%d') as xdate_only 
FROM tracking
WHERE project_id = " . (int) $proj . "
AND `date` BETWEEN . " . $startDate . " AND " . $endDate . "
GROUP BY DATE(`date`)

 

put the dates of the range in $startDate and $endDate respectively. I assumed `date` is the date column you are using - by going over the range and grouping per day you get the same result.

[Zend - Your thought and experiences]

I've been using the Zend Framework since ver. 0.2 and I can really credit a lot of my growth as a developer to it. Obviously not perfect, it's still the best written PHP framework on the market.

 

to Thorpe and others who experienced performance issues - I can't comment on your specific situation, but from my experience the framework was never the bottleneck. One of our ZF projects scaled from one dedicated server that was squeezed to the max to now 28 servers and growing. We encountered database bottleneck, apache bottlenecks, memory bottlenecks, network issues, but the framework was never the problem. If anything, the high maintainability of code written with ZF has allowed us to quickly evaluate and optimize bottlenecks easily.

 

I'd really recommend the ZF to anyone who wants to get started with a framework - just a word of caution, there is some learning curve involved. Just brace yourself and you'd reap the rewards in the long run.