grandadevans
-
Posts
30 -
Joined
-
Last visited
Never
Posts posted by grandadevans
-
-
Got IT!
I changed it from
[code]
<?php require_once('Connections/campaign.php'); ?>
<?php
$company=$_POST['id'];
mysql_select_db($database_campaign, $campaign);
$query_companies = "SELECT DISTINCT entryID, company, companyEmail, URL FROM BFPO_unfriendly WHERE company IS NOT NULL";
$companies = mysql_query($query_companies, $campaign) or die(mysql_error());
$row_companies = mysql_fetch_assoc($companies);
$totalRows_companies = mysql_num_rows($companies);
$query_companyDetails=sprintf("SELECT entryID, company FROM BFPO_unfriendly WHERE companyEmail LIKE '$company';");
$companyDetails=mysql_query($query_companyDetails, $campaign) or die (mysql_error());
$row_companyDetails=mysql_fetch_assoc($companyDetails);
?><?php
if ($_POST['Submit']) {
require("phpmailer/class.phpmailer.php");
$companyID=$row_companyDetails['entryID'];
$IP=$_SERVER['REMOTE_ADDR'];
$email=$_POST['email'];
$name=$_POST['name'];
$message=$_POST['description'];
$subject=$_POST['subject'];
if ($_POST['id'] !== 'all') {
//Start of email to individual companies
if (isset($email)){
$insert ="INSERT INTO emails_sent (companyID, name, email, message, IP) VALUES ('$companyID', '$name', '$email', '$message', INET_ATON('$IP'));";
mysql_query($insert) or die(mysql_error());
$loc='forms2.php?sent=' . $row_companyDetails['company'];
header("location: $loc");
[/code]
to
[code]<?php
require_once('Connections/campaign.php');
$company=$_POST['id'];
mysql_select_db($database_campaign, $campaign);
$query_companies = "SELECT DISTINCT entryID, company, companyEmail, URL FROM BFPO_unfriendly WHERE company IS NOT NULL";
$companies = mysql_query($query_companies, $campaign) or die(mysql_error());
$row_companies = mysql_fetch_assoc($companies);
$totalRows_companies = mysql_num_rows($companies);
$query_companyDetails=sprintf("SELECT entryID, company FROM BFPO_unfriendly WHERE companyEmail LIKE '$company';");
$companyDetails=mysql_query($query_companyDetails, $campaign) or die (mysql_error());
$row_companyDetails=mysql_fetch_assoc($companyDetails);
if ($_POST['Submit']) {
require("phpmailer/class.phpmailer.php");
$companyID=$row_companyDetails['entryID'];
$IP=$_SERVER['REMOTE_ADDR'];
$email=$_POST['email'];
$name=$_POST['name'];
$message=$_POST['description'];
$subject=$_POST['subject'];
if ($_POST['id'] !== 'all') {
//Start of email to individual companies
if (isset($email)){
$insert ="INSERT INTO emails_sent (companyID, name, email, message, IP) VALUES ('$companyID', '$name', '$email', '$message', INET_ATON('$IP'));";
mysql_query($insert) or die(mysql_error());
$loc='forms2.php?sent=' . $row_companyDetails['company'];
header("location: $loc");
[/code]
And now it works, I've undone and redone it a few times to make sure and the problem is solved.
looking at it, it may have been the indent before the
[code]
<?php require_once('Connections/campaign.php'); ?>[/code]
but then surely it would have come up with an error saying that the header was already sent -
That echoes the right URL
[code]forms2.php?sent=Argos[/code]
but it just echoes it, it still skips the header.
I've tried a few things.
I put a header at the head of the page and it worked fine.
I've moved the script as far up the page as possible and it still does not work, the only code infornt of it is
[code]<?php
require_once('Connections/campaign.php');
$company=$_POST['id'];
mysql_select_db($database_campaign, $campaign);
$query_companies = "SELECT DISTINCT entryID, company, companyEmail, URL FROM BFPO_unfriendly WHERE company IS NOT NULL";
$companies = mysql_query($query_companies, $campaign) or die(mysql_error());
$row_companies = mysql_fetch_assoc($companies);
$query_companyDetails=sprintf("SELECT entryID, company FROM BFPO_unfriendly WHERE companyEmail LIKE '$company';");
$companyDetails=mysql_query($query_companyDetails, $campaign) or die (mysql_error());
$row_companyDetails=mysql_fetch_assoc($companyDetails);
if ($_POST['Submit']) {
require("phpmailer/class.phpmailer.php");
$companyID=$row_companyDetails['entryID'];
$IP=$_SERVER['REMOTE_ADDR'];
$email=$_POST['email'];
$name=$_POST['name'];
$message=$_POST['description'];
$subject=$_POST['subject'];
if ($_POST['id'] !== 'all') {
[/code]
This one has got me totally flustered -
The
[code]if ($_POST['submit']){
[/code]
didn't work as it didn't insert anything in the database.
I changed it to
[code]if (isset($_POST['email'])){
[/code]
which is a required field and that inserted the record into the db but skipped the header.
It obviously recognises the $loc as when the page refreshes it echoes the $loc as requested.
Here is the code that I have just used.
[code]if (isset($_POST['email'])){
$insert ="INSERT INTO emails_sent (companyID, name, email, message, IP) VALUES ('$companyID', '$name', '$email', '$message', INET_ATON('$IP'));";
mysql_query($insert) or die(mysql_error());
$loc='forms2.php?sent=' . $row_companyDetails['company'];
header("location: $loc");
}
echo $loc;
[/code] -
That also works
-
That work's fine.
I have several headers on my site already and have never had a problem that I've not been able to work through by myself before but this one is getting the best of me. -
I've just tried
[code] <?php
$loc='forms2.php?sent=' . $row_companyDetails['company'];
if (isset($loc)) {
header("Location: test.php");
?>
[/code]
and
[code]<?php
if (isset($insert)) {
header("Location: forms2.php?sent=' . $row_companyDetails['company']");
}
?>[/code]
For the second one I get a Parse error
Remember that I'm only uising test.php to see if it is definitively failing. -
Nope,
Still not working
This is the actual code that I am using, I was just using test.php as an example to see if it would go to the URL as it would have been a 404 error and easier to spot than looking at the address bar.
[code] $insert ="INSERT INTO emails_sent (companyID, name, email, message, IP) VALUES ('$companyID', '$name', '$email', '$message', INET_ATON('$IP'));";
mysql_query($insert) or die(mysql_error());
$loc='forms2.php?sent=' . $row_companyDetails['company'];
if (isset($loc)) {
header("Location: $loc");
}
echo $loc;
[/code] -
Hi all,
Can anybody help me,
I have a script, here are the relevant lines
[code] mysql_query($insert) or die(mysql_error());
$loc='test.php;
header("Location: $loc");
echo "test";
[/code]
The problem is that it looks like it is just skipping the header line.
After the form has been completed, it completes the MySQL query, just refreshes the same page and echoes the "test" line.
There is no error message or anything, it just skips the line, I have tried it this way and with a direct reference to a page ie
[code]header("Location: test.php") [/code]
but that doesn't work either!
Anybody got any ideas? -
Cheers mate,
I've also implemented the suggestion about storing IP addresses.
John -
I tried several combinations of that but couln't do it with getting a 1,2,3,4,5 etc no matter how I ordered the subquery
John -
GOT IT!
The books have come in handy for once.
The solution I have is to create a couple of temporary tables then use them like so
[code]
SET @posLastWeek :=0;
SET @posThisWeek :=0;
DROP TABLE IF EXISTS lastWeek;
DROP TABLE IF EXISTS thisWeek;
CREATE TEMPORARY TABLE lastWeek
SELECT @posLastWeek :=@posLastWeek+1 AS pos, c.companyID, COUNT(c.clickID) AS clicksLW
FROM click_log c WHERE WEEK(c.`date`)=WEEK(CURDATE())-1 AND companyID !=0
GROUP BY c.companyID ORDER BY clicksLW DESC LIMIT 10;
CREATE TEMPORARY TABLE thisWeek
SELECT @posThisWeek :=@posThisWeek+1 AS pos, c.companyID, COUNT(c.clickID) AS clicksTW
FROM click_log c WHERE WEEK(c.`date`)=WEEK(CURDATE()) AND companyID !=0
GROUP BY c.companyID ORDER BY clicksTW DESC LIMIT 20;
SELECT * FROM lastWeek;
SELECT * FROM thisWeek;
SELECT t.pos, b.company, t.clicksTW, l.pos, l.clicksLW
FROM thisWeek t
LEFT JOIN lastWeek l USING (companyID)
LEFT JOIN bfpomerchants b USING (companyID)
ORDER BY t.pos ASC;
DROP TABLE lastWeek;
DROP TABLE thisWeek;[/code]
Resulting in
[code]
+------+--------------------------------+----------+------+----------+
| pos | company | clicksTW | pos | clicksLW |
+------+--------------------------------+----------+------+----------+
| 1 | Let's Go Retro | 12 | NULL | NULL |
| 2 | Surplus and Adventure Ltd. | 11 | 1 | 64 |
| 3 | Amazon | 11 | 10 | 9 |
| 4 | Woolworths | 9 | NULL | NULL |
| 5 | Sports Warehouse | 6 | NULL | NULL |
| 6 | Primrose Aromatherapy | 6 | NULL | NULL |
| 7 | Sportsshoes Unlimited | 5 | NULL | NULL |
| 8 | Mean and Green | 5 | NULL | NULL |
| 9 | Marks and Spencers | 4 | NULL | NULL |
| 10 | Island-Leisure | 4 | NULL | NULL |
| 11 | Teddy Love | 4 | NULL | NULL |
| 12 | Spritz | 4 | NULL | NULL |
| 13 | Gadget Pages Ltd | 3 | NULL | NULL |
| 14 | Bookplace Ltd | 3 | NULL | NULL |
| 15 | Penrith Survival Equipment Ltd | 3 | NULL | NULL |
| 16 | BrightMinds | 3 | NULL | NULL |
| 17 | Moonpig | 3 | 5 | 17 |
| 18 | Physio Supplies | 3 | NULL | NULL |
| 19 | Clean Store | 3 | NULL | NULL |
| 20 | Cadbury Gifts Direct | 2 | NULL | NULL |
+------+--------------------------------+----------+------+----------+[/code]
Cheers Fanway.
If anybody has any easier ways to do it feel free to let me know.
John -
Hello everybody yet again,
I have a table which logs
A) The users IP address
B) The company ID of the company clicked
The table looks like this
[code]
+-----------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-------------------+----------------+
| clickID | int(11) | NO | PRI | NULL | auto_increment |
| date | timestamp | YES | MUL | CURRENT_TIMESTAMP | |
| companyID | int(11) | YES | MUL | NULL | |
| URL | varchar(255) | YES | | NULL | |
| referer | varchar(50) | YES | MUL | NULL | |
| IPAddress | varchar(100) | YES | | NULL | |
| agent | varchar(255) | NO | MUL | | |
+-----------+--------------+------+-----+-------------------+----------------+[/code]
How do I select instances of the same IP address with the same compay ID
for example, what I want to do is
DELETE rows where the same IP address and companyID combination appear more than 4 times.
Can anybody help me out?
Thanks
John -
Nice One,
Cheers mate, that works a treat but now I've run into another problem.
The statement I have currently looks like this
[code]
SELECT b.company, b.companyID, @posThisWeek :=@posThisWeek+1 AS posThisWeek,
(SELECT COUNT(c.clickID) FROM click_log c
WHERE c.companyID=b.companyID AND WEEK(c.`date`)=WEEK(CURDATE())-1
GROUP BY c.companyID LIMIT 1) AS clicksLastWeek,
(SELECT COUNT(c.clickID) FROM click_log c
WHERE c.companyID=b.companyID AND WEEK(c.`date`)=WEEK(CURDATE())
GROUP BY c.companyID LIMIT 1) AS clicksThisWeek
FROM bfpomerchants b ORDER BY clicksThisWeek DESC LIMIT 10;[/code]
This gives me the top 10 with number of clicks per company for the last 2 full weeks.
My problem is that I can't figure out how to get the position of
'clicksLastWeek'
as every time I try the @ statement it show the record number (obviously, and that is what I asked for!)
It works fine for this weeks position but do you know how I could get the position of the week before, Ive tried it in subqueries but can't do it with only 1 column.
Any ideas.
Thanks again
John -
Hi, thanks for your help
When I tried the code [code]SET @rowNumber := 0;
SELECT @rowNumber=rowNumber+1 AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC[/code]
It came up with something like 'unknown colum rowNumber'
I tried all sorts of variations including
[code]SELECT @rowNumber=@rowNumber+1;[/code]
But that just keeps coming up with '0' as the answer.
Do you have any idea why or what I can do
I have tried setting the @rowNumber in a subquery but it won't let me.
John -
Sorry mate,
Ive read looked all through all the documentaion I have on SQL and I can't find an answer anywhere or even figure out how JOINs can help.
Could you please explain more or point me more in the right direction
Thanks again,
John -
Hi all,
I have a question, is there a way of retreaving the row number of a Recordset Row number eg
[code]SELECT [code required] AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber ASC[/code]
To come up with
[code]
|--------------------------------|
|rowNumber | otherNumber |
|--------------------------------|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
|--------------------------------|[/code]
and
[code]SELECT [code required] AS rowNumber, a.otherNumber FROM a ORDER BY a.otherNumber DESC[/code]
To come up with
[code]
|--------------------------------|
|rowNumber | otherNumber |
|--------------------------------|
| 1 | 3 |
| 2 | 2 |
| 3 | 1 |
|--------------------------------|[/code]
I have books on SQL galore but I cannot find it, anybody any ideas?
Thanks
John -
After all that it was just a simple query
[code]SELECT bfpomerchants.companyID, bfpomerchants.company,
(SELECT COUNT(*) FROM click_log
WHERE click_log.companyID = bfpomerchants.companyID AND
MONTH(`date`) = MONTH(CURDATE())-1) AS lastMonth,
(SELECT COUNT(*) FROM click_log
WHERE click_log.companyID = bfpomerchants.companyID AND
MONTH(`date`) = MONTH(CURDATE())) AS thisMonth
FROM bfpomerchants
ORDER BY thisMonth DESC[/code] -
[b]FIXED![/b][size=1][color=#CC0000]
Hi,
I am in desperate need for help.
I am trying to create a page with a top 20 chart.
The problem I am having is that I can't get the COUNTS for last month and this months
ordered by the position this month descending.
The fields I am using are :
Table --> bfpomerchants
[blockquote]Appropriate fields --> companyID (INT) & company (VARCHAR)[/blockquote]
Table --> click_log
[blockquote]Appropriate fields --> clickID (INT), date (timestamp) & companyID (INT)[/blockquote]
I have tried all sorts of JOINS, COUNTS and every other method I know of.
I have figured out the
[code]WHERE MONTH(`date`) = MONTH(CURDATE())-1[/code]
for last month, and without the -1 for this month.
I can get the values for last month and this month from seperate statements,
but I need them in a seperate statement
This is what I'm using to get them seperately
[code]SELECT COUNT(c.clickID) AS lastMonth, b.company
FROM bfpomerchants b RIGHT JOIN click_log c USING (companyID)
WHERE MONTH(`date`) = MONTH(CURDATE())-1 AND b.company IS NOT NULL
GROUP BY b.company
ORDER BY lastMonth DESC;[/code]
I have tried doing it myself but ended up with a statement that didn't work and was taking up to
and just over 180 seconds.
Can anybody help me, if you need any info on the field structure then just ask.
Thanks
John -
Right, I'm probably gonna sound like a complete muppet here but I have fixed it.
After reading the advice of 2 people who know better than me, I got to thinking.
The problem had to be somewhere in the script and it turned out to be a misplaced } bracket that meant it was trying to input the statement twice.
Time to learn a lesson.
Thanks again to all
John -
That's just it, out of all the 4 usernames that she tried to enter, only the last one went through but
There is NO sara'a apart fom her
I have just confirmed it with a %sara% query, and there are only 60 something users so the list id not that extensive. -
Hi,
Can anybody help me? On my site there is a registration page, occassionaly I see that people are reloading the registration page 3 or 4 times but I cannot see why, I have contacted a few members who persisted until the entry went through and asked if they had any problems, to cut a long story short....
The describe the fault as coming up with
Key 2 'username' already exists.
Obviously I have a field called username in the users database which is a unique key but the users have had to enter 3 or 4 entries until they succeed, the last one had to enter
sara
saratrolley
saraanne
sara1
Only the last one succeeded.
On some users including whenever I test it through IE or Firefox it works fine, on others......well, you tell me!
Does anyone have any ideas as to why this is happening
This is the form field
[code]<input name="username" type="text" id="username" value="<?php echo $_POST['username'];?>" />[/code]
This is the input statement
[code] $insertSQL = sprintf("INSERT INTO users (username, password, fName, sName, email, phoneType1, phoneNo1, phoneType2, phoneNo2, rank, corpRegt, unit, area, countryID, newsletter, `level`, dob, dateJoined, icon) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, '$newDate', '$date', '$thumbnail')",
GetSQLValueString($_POST['username'], "text"),
GetSQLValueString($_POST['password'], "text"),
GetSQLValueString($_POST['fName'], "text"),
GetSQLValueString($_POST['sName'], "text"),
GetSQLValueString($_POST['email'], "text"),
GetSQLValueString($_POST['phoneType'], "text"),
GetSQLValueString($_POST['phoneNo'], "text"),
GetSQLValueString($_POST['phoneType2'], "text"),
GetSQLValueString($_POST['phoneNo2'], "text"),
GetSQLValueString($_POST['rank'], "text"),
GetSQLValueString($_POST['regt'], "text"),
GetSQLValueString($_POST['unit'], "text"),
GetSQLValueString($_POST['area'], "text"),
GetSQLValueString($_POST['country'], "int"),
GetSQLValueString($_POST['newsletter'], "text"),
GetSQLValueString($_POST['level'], "text"));
[/code]
and this is the describe from the users table
[code]mysql> DESCRIBE users;
+------------+--------------+------+-----+-----------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-----------------+----------------+
| userID | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(100) | NO | UNI | | |
| password | varchar(100) | NO | | | |
| fName | varchar(100) | NO | | | |
| sName | varchar(100) | NO | | | |
| email | varchar(255) | NO | | | |
| phoneType1 | varchar(15) | YES | | NULL | |
| phoneNo1 | varchar(30) | YES | | NULL | |
| phoneType2 | varchar(15) | YES | | NULL | |
| phoneNo2 | varchar(30) | YES | | NULL | |
| icon | varchar(255) | YES | | images/user.gif | |
| dob | date | YES | | NULL | |
| dateJoined | date | YES | | NULL | |
| rank | varchar(100) | YES | | NULL | |
| corpRegt | varchar(255) | YES | | NULL | |
| unit | varchar(255) | YES | | NULL | |
| area | varchar(255) | YES | | NULL | |
| countryID | int(11) | YES | MUL | NULL | |
| newsletter | varchar(10) | YES | | NULL | |
| level | varchar(100) | YES | | user | |
| posts | int(11) | YES | | 0 | |
| lastLogin | date | YES | MUL | NULL | |
+------------+--------------+------+-----+-----------------+----------------+[/code] -
God Damnit!
I could have sworn that I had tried that.
What a day 1, week 1 schoolboy error.
Cheers mate
By the way.
How do I get the Fixed thing on the title? -
Hi all,
My head is about to explode. I have been trying to figure this out now for a few days.
The query below works fine but I have another field I need to add.
I have another table called click_log which logs the companyID clicked, what I need to do is add the number of cliks for that company to the query.
I have tried COUNT(click_log.companyID) in alsorts of variations but I cannot get it right.
Can anybody help. I have laid the code out as clear as I can below. If any more info is needed just give us a shout.
Cheers
[code]
SELECT
bfpomerchants.companyID,
bfpomerchants.company,
bfpomerchants.cat1ID,
bfpomerchants.cat2ID,
bfpomerchants.cat3ID,
DATE_FORMAT(bfpomerchants.date, '%%D %%b %%y') AS dateAdded,
LEFT(bfpomerchants.`desc`, 100) AS short,
bfpomerchants.URL,
bfpomerchants.company,
(SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat1ID = bfpocats.catID ) AS cat1,
(SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat2ID = bfpocats.catID ) AS cat2,
(SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat3ID = bfpocats.catID ) AS cat3,
(SELECT bfpocats.cat FROM bfpocats WHERE bfpomerchants.cat4ID = bfpocats.catID ) AS cat4
FROM
bfpomerchants
WHERE
(bfpomerchants.cat1ID = '%s') OR
(bfpomerchants.cat2ID = '%s') OR
(bfpomerchants.cat3ID = '%s') OR
(bfpomerchants.cat4ID = '%s')
[/code] -
Cheers mate,
I have already had to learn CCS, PHP & SQL in the past 3 or 4 months so I think I'll take a break from learning new languages and come back to it in a few weeks.
I'll certainly bookmark the site though, thanks for the link.
header line is being skipped
in PHP Coding Help
Posted
I had the whitespace problem only last week, but I know what to look for when I get that 'header already sent error' and I've changed nothing since then.