[Select Query Problems]

Ok, confused again. I agree with the unique id's, but we were not talking about unique id's. (Where we?)

[Select Query Problems]

Yes barand, the default setting is 1024 and it will truncate at that point. But, it is a setting and it can be changed. Much like the default upload size in PHP is two meg but you usually want to always change that. Just a part of performance tuning. I don't expect newbies to know about it let alone how do it. I am sure you would agree to avoid sub queries when possible.

 

A bit confused why you point out about the original database having a datetime column. The db that you and I both used also has a datetime. The only difference is I didnt use date time to find the last record which as I pointed out will not work if there are two records with the same date time for a given prospect.

[Select Query Problems]

Sub-query's can take a toll on your database. One thing to point out about @Barands solution is that since it is based on datetime rather than the actual last record inserted per prospect, if there are two records with the same exact date-time for a given prospect, you are going to get more than one result for that prospect. Per your specs, you just wanted one result per prospect only and that being the LAST record.

 

Here is an example how to do it that will not take a sub-query toll on the DB and will give you the actual last record inserted per prospect and is a lot less code.

 

* I didn't see that you were trying to get the staff_id or the comment date so I left it out.

SELECT p.*, 
       Substring_index(Group_concat(n.`comment` ORDER BY n.note_id DESC), ',', 1 ) AS last_comment
FROM   database_prospects AS p 
       INNER JOIN database_prospect_notes AS n ON p.prospect_id = n.prospect_id 
WHERE  p.active = 1 
GROUP  BY p.prospect_id 

[creating new tables]

@hansford, not sure what your post has to do with the limited info the OP provided, but there would need to be four more tables needed for your example for a properly normalized DB. Another one for address, another one phones along with a phones type table, another one email/electronic communications, and another one note, all of which has nothing to do with "Person" data.

 

Person data would be first, middle and last name, sex, birthdate, hair color, eye color, height and weight, ethnicity, active/inactive, marital status (unless you are tracking those over time, then separate tables for those)

[creating new tables]

Perhaps you should detail exactly what you have and exactly what you are trying to accomplish. I for one don't understand. It would greatly help if you posted an SQL dump of your "Normalized Master Database" along with sample data and details of what you want to do with it. "Analyze" doesn't tell us anything, neither does time series.

 

Regardless of what your doing, creating all those tables is just wrong. Doing the wrong thing never saves time.

[creating new tables]

NO, NO, NO!! You are forbidden to do that! You need to study database normalization.

[Select Query Problems]

Here is the MySQL version to get you up and running. You should always use lowercase column names. You have some all lower, some upper and lower

 

SQL

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for database_prospect_notes
-- ----------------------------
DROP TABLE IF EXISTS `database_prospect_notes`;
CREATE TABLE `database_prospect_notes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prospect_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `comment` mediumtext NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`note_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of database_prospect_notes
-- ----------------------------
INSERT INTO `database_prospect_notes` VALUES ('1', '1', '1', 'Police check uncovered issues?', '2015-10-15 12:02:33');
INSERT INTO `database_prospect_notes` VALUES ('2', '1', '236', 'Appointed', '2015-10-15 14:29:27');

-- ----------------------------
-- Table structure for database_prospects
-- ----------------------------
DROP TABLE IF EXISTS `database_prospects`;
CREATE TABLE `database_prospects` (
  `prospect_id` int(11) NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(20) NOT NULL,
  `LastName` varchar(20) NOT NULL,
  `State` varchar(3) NOT NULL,
  `Active` int(1) NOT NULL,
  `Email` varchar(100) NOT NULL,
  `UserType` varchar( NOT NULL DEFAULT 'Prospect',
  `dlr_group` varchar(10) NOT NULL,
  `middlename` varchar(30) NOT NULL,
  `carname` varchar(80) NOT NULL,
  `tradingname` varchar(80) NOT NULL,
  `arn` int(6) NOT NULL,
  `carn` int(6) NOT NULL,
  `authorisations` varchar(20) NOT NULL,
  `phonehome` varchar(15) NOT NULL,
  `phonework` varchar(15) NOT NULL,
  `phonemobile` varchar(15) NOT NULL,
  `faxwork` varchar(15) NOT NULL,
  `website` varchar(80) NOT NULL,
  `paddress` varchar(50) NOT NULL,
  `psuburb` varchar(30) NOT NULL,
  `pstate` varchar(3) NOT NULL,
  `ppostcode` varchar(4) NOT NULL,
  `haddress` varchar(50) NOT NULL,
  `hsuburb` varchar(30)  NOT NULL,
  `hstate` varchar(3)  NOT NULL,
  `hpostcode` varchar(4)  NOT NULL,
  `baddress` varchar(50)  NOT NULL,
  `bsuburb` varchar(30)  NOT NULL,
  `bstate` varchar(3) NOT NULL,
  `bpostcode` varchar(4) NOT NULL,
  `raddress` varchar(50) NOT NULL,
  `rsuburb` varchar(30)  NOT NULL,
  `rstate` varchar(3)  NOT NULL,
  `rpostcode` varchar(4)  NOT NULL,
  `dob` date DEFAULT NULL,
  `placeofbirth` varchar(50)  NOT NULL,
  `split` varchar(20)  NOT NULL,
  `abn` varchar(15)  NOT NULL,
  `exdealer` varchar(40)  NOT NULL,
  `software` varchar(30) NOT NULL,
  `softwaremodules` varchar(30)  NOT NULL,
  `research` varchar(30)  NOT NULL,
  `Income` varchar(20)  NOT NULL,
  `Probability` varchar(4)  NOT NULL,
  `Cashflow` varchar(20) NOT NULL,
  `added` date NOT NULL,
  PRIMARY KEY (`prospect_id`),
  KEY `id_2` (`prospect_id`),
  KEY `id` (`prospect_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of database_prospects
-- ----------------------------
INSERT INTO `database_prospects` VALUES ('1', 'Smith', 'John', 'NSW', '1', '', 'Prospect', '--', '', 'John Smith', '', '0', '0', '--', '', '', '', '', '', '', '', '--', '', '', '', '--', '', '', '', '--', '', '', '', '--', '', '0000-00-00', '', '', '', '', '', '', '', '$15,000', '100%', '15,000', '2015-10-15');

Query to get some data

SELECT d.firstname,
       d.lastname,
       d.state,
       d.active,
       d.prospect_id,
       d.dlr_group,
       d.income,
       d.probability,
       d.added,
       n.comment AS LastOfcomment
FROM
database_prospect_notes AS n
LEFT JOIN database_prospects AS d ON n.prospect_id= d.prospect_id
WHERE d.active=1
ORDER BY n.note_id DESC LIMIT 1

[Select Query Problems]

Some Formatting would make it much more readable. Post an sql dump of your tables and data for us to test on. Mysql doesnt not have a "Last" function

SELECT database_prospects.firstname, 
       database_prospects.lastname, 
       database_prospects.state, 
       database_prospects.active, 
       database_prospects.prospect_id, 
       database_prospects.dlr_group, 
       database_prospects.income, 
       database_prospects.probability, 
       database_prospects.added, 
       Last(database_prospect_notes.comment) AS LastOfcomment 
FROM   database_prospect_notes 
       RIGHT JOIN database_prospects 
               ON database_prospect_notes.prospect_id = 
                  database_prospects.prospect_id 
GROUP  BY database_prospects.firstname, 
          database_prospects.lastname, 
          database_prospects.state, 
          database_prospects.active, 
          database_prospects.prospect_id, 
          database_prospects.dlr_group, 
          database_prospects.income, 
          database_prospects.probability, 
          database_prospects.added 
HAVING (( ( database_prospects.active ) = 1 ))

[Selecting a mysql server at registration]

Que Jaw drop  . That script has nothing whatsoever to do with what you just posted about..

 

That script is also vulnerable to SQL Injection.

[Selecting a mysql server at registration]

You are using obsolete MySQL code that will not work at all in the latest version Php. You need to use PDO with prepared statements.

[PHP code writing format?]

Here is a page that shows you some of the various formatting standards

 

http://www.terminally-incoherent.com/blog/2009/04/10/the-only-correct-indent-style/

[Trying to understand query]

The t1, t2 etc are specifically ALIASES. Say your name is mike(table_name) but your wife calls you bigdaddy(alias).

 

It is a shortcut so you dont have repeatedly type out the whole table name.

 

Example:

SELECT shortname.id, shortname.firstname FROM mysuperlongtable AS shortname

 

Instead of

SELECT mysuperlongtable.id, mysuperlongtable.firstname FROM mysuperlongtable

 

And in practice

SELECT s.id, s.firstname FROM mysuperlongtable AS s

 

The ALIAS can be anything you want it to be and is generally only one or two characters

 

I have always found the t1, t2 way to generic and undescript. If the table is users, I would use the ALIAS u, if the table is books. I would use the ALIAS b

 

 

Alias used to confuse me too, especially because of t1, t2

[Session expires too soon]

And here i was thinking this was somewhere I could reach out to get help.

 You did get help. We hopefully stopped you from using bad code. That was step 1.

[randomizing (shuffle)]

Where are the API docs? That would be a good start as to what can easily be done. Post a link to the API.

[randomizing (shuffle)]

I assume $results is from an SQL query so you would do ORDER BY RAND()

[Session expires too soon]

By the powers vested in my by no one, I command all knowledgeable coders to not help get this code "working". As @Jacques1 so wisely said, this is evil dirty, bad, nasty code. If I was a mod, this thread would be locked after @Jacques1 answered.

[randomizing (shuffle)]

Randomize $results

[Session expires too soon]

So much to say, but a bit short on time at the moment. The biggest thing is that you are using obsolete MySQL code. You need to use Pdo with prepared statements. Your database structure could use improvement as well.

 

You are also storing the timestamp data incorrectly. MySQL has a time stamp data type. You should be using that.

 

Your code is also vulnerable to SQL injection. You never ever send user-supplied data directly to the database.

[HTML select and option work with PHP database]

If you're storing the month name in the database. Your storing the data wrong

[HTML Form data to CSV file using PHP]

Yeah, that is the simplest way, just create the file with the header, pretty redundant to keep checking if the file exists since after the first run, it will always exist.

[HTML Form data to CSV file using PHP]

@hansford,

 

Another problem with your version on appending, if a user submits empty fields it will throw off the column order of the data since you are only appending data that is not empty. I would like to see what you would do for appending with header row.

[HTML Form data to CSV file using PHP]

The OP never mentioned if it was a one time write or not. One time all good, but JUST changing the flag to 'a' is going to add the headers over and over. If the OP is doing multiple writes (appending) there would need to be a file exists check so the header is not continuously added.

[HTML Form data to CSV file using PHP]

Scratch the last paragraph. b mode is still an option. Further down..

 

For portability, it is strongly recommended that you always use the 'b' flag when opening files withfopen().

 

I ALWAYS use Mysql so I dont use these functions.

[HTML Form data to CSV file using PHP]

@hansford

Nice job with array_keys & array_values but its not going to work with multiple rows. When appending you are going to repeatedly insert the header row.

 

Also the b parameter is no longer an option in newer PHP. Per the Manual:

 

As of PHP 4.3.2, the default mode is set to binary for all platforms that distinguish between binary and text mode. If you are having problems with your scripts after upgrading, try using the 't' flag as a workaround until you have made your script more portable as mentioned before

[How to Create a "action.php" page for HTML Form?]

@valandor, How about we alternate. You take your meds, I answer questions, I take your meds, you answer questions. The cops show up, we dont answer any questions.