[PHP-DB] Re: looping code
[EMAIL PROTECTED] wrote:
Hello all I have a helpdesk application that was written in php and I
would like to create a script that will e-mail the systems staff how many
open helpdesk tickets they have. There are 10 users and I can wrtie a
script no problem to do a count and then e-mail that to a user I just have
no idea how the heck to loop it through all of the users .
Here is my code for one user if they are logged in if anyone can help me
or lead me in the right direction I would appreicate it very much my brain
is just not working today:
why don't you use mysql to do the heavy lifting? you could use COUNT
and GROUP BY to figure out the whole thing in one go pretty much.
function showSummary() {
global $user, $mysql_link;
$openTickets = 0;
$userTickets = 0;
$query = SELECT events.e_id, events.t_id, events.e_status, .
events.e_assignedto, ticket.t_id FROM events,ticket .
WHERE events.t_id = ticket.t_id ORDER BY .
events.t_id, events.e_id;;
$result = query( $query );
i think your query should be more like
select e.e_status, e.e_assignedto, count(1) from events e group by
e.e_assignedto, e.e_status
then you want to do
$result=mysql_query($query) or die(mysql_error());
ALWAYS check the return value of mysql_query. it will save you from
much headscratching later on if you make a silly typo or something.
the rest of the script would be something like:
$user=Array();
while ($r=mysql_fetch_assoc($result)) {
$user[$r['e_assignedto']][$r['e_status']] += 1;
}
foreach ($user as $u) {
$count = 0;
print Ticket summary for $u:\n;
foreach ($user[$u] as $x) {
print Ticket type $x: . $user[$u][$x] . \n;
$count += $user[$u][$x];
}
print Total tickets: $count\n;
}
basically you create an array of users, each entry in the array is
another array containing the ticket status and a count.
if you want to see it visually, do print_r($user) right before the first
foreach.
-jsd-
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Don't know why query works this way
Aaron Wolski wrote:
Here's some functions I use in my development which save on connect and
query calls for me.
aaron: i do more or less the same thing. allow me to criticize your
functions in one very important respect: you do not check the return
value of your calls! i would suggest that anyone doing mysql work
ALWAYS check the return value. it will save you MUCH head scratching
later on down the line. really.
eg:
function db_connect() {
mysql_pconnect(localhost,username,password);
mysql_select_db(Database);
}
function db_connect {
mysql_pconnect('localhost','username','password') or
die(mysql_error());
mysql_select_db('database') or die(mysql_error());
}
and so forth.
-jsd-
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Not sure where this messages goes, but I have a question
fred jones wrote: Okay the weird part is that everything seems kosher, (no errors, the output from the select statement prints out the data I entered), the only problem being when I look in the database (via a webadmin or bringing up the data in another form) it's not there! In another words I'm looking at the web page when this exits, and it prints out data, while I'm simultaneously looking at the webadmin (just refreshed), and the updated field not only wasn't updated, any information in the database is now blank. So it's affecting the row, just not the way I want =P. Also once I go to the next page, do a select, print it out, etc. nothing is there. you need to COMMIT after you do the insert. http://www.mysql.com/doc/en/COMMIT.html -jsd- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] sqlite functions
The function sqlite_fetch_all() mentioned in the changelog of version 1.0 of the extension is not documented in php docs. I'd like to know what is the diference between sqlite_fetch_all() and sqlite_array_query() about which the docs say that [it] is best suited to queries returning 45 rows or less. If you have more data than that, it is recommended that you write your scripts to use sqlite_unbuffered_query() instead for more optimal performance. Does sqlite_fetch_all() have the same restriction? Is it the recommended function for large amounts of data as sqlite_unbuffered_query()? TIA Regards, Andu Novac -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How to do error feedback when adding to indexed table?
I'm not sure how to explain this, I'm totally new to MySQL and SQL as a
whole, this is my first ever code using SQL at all...so plz bare with me
here...
I'm working with PHP 4.2.3 and MySQL 4.something (not certain what version
the end-server runs).
For a game clan I'm in (MPE 1A in Earth: 2025 - 1a), I'm working on a DB to
store some rather game-specific info. It's only a single table in the DB,
but the table is indexed by the field 'id'.
Whenever someone wants to add data to the table, they fill out a form, hit
submit and either get an error about wrong or missing data, or if ok get a
preview before being able to finally add it to the table. As the script is
now, it will attempt to add the data to the table, but if already there,
nothing happens, and there's no response to the user about it.
I want it to give a feedback message that the data is already in the table,
and bump ppl back to the edit form with that message. For the other things
the script checks, all it does is load the variable $error with the
error-message and set a phase-flag to the right value, and then the page
generation part knows that there's been an error, and regenerates the entry
form with the submitted data. So what I want, is to have the add data part
to just change the two variables needed for the error system to see there's
something wrong, but I've got no idea what I need to change in the code to
do that. I've searched google and the archives, but come up with nothing...
Here's the add-data part of the code...the variables in the comment is just
so that I know what kind of data are in each variable, rather than what
they're actually set to in the DB:
elseif ($phase == 3) {
/*
num / id = int
country = string
nw = int
gov = string
strat = string
ally = string
spies = int
totdef = int
totoff = int
owner = string
*/
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die('Unable to select database');
$query = INSERT INTO ally2ally VALUES
('.$_POST['num'].','.$_POST['country'].','.$_POST['nw'].','.$_POST['gov'].','.$_POST['strat'].','.$_POST['spies'].','.$_POST['totdef'].','.$_POST['totoff'].','.$_POST['ally'].','.$_POST['owner'].');
mysql_query($query);
mysql_close();
}
the error message part is simply this:
?php
// if there is an error in the submitted data, display the error message
if (isset($error)) {
?
table
tr
td?php echo($error); ?/td
/tr
/tablebr
br
?php
}
?
The rest of the code is not essential in this aspect. I just need some help
to figure out how to make the script tell the user that it can't add the
data because the ID is already in the database...(that's the first field btw).
TIA
Rene
--
Rene Brehmer
aka Metalbunny
http://metalbunny.net/
References, tools, and other useful stuff...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] How to do error feedback when adding to indexed table?
Rene,
-{ Rene Brehmer }- said:
I'm not sure how to explain this, I'm totally new to MySQL and SQL as a
whole, this is my first ever code using SQL at all...so plz bare with me
here...
I'm working with PHP 4.2.3 and MySQL 4.something (not certain what version
the end-server runs).
For a game clan I'm in (MPE 1A in Earth: 2025 - 1a), I'm working on a DB
to
store some rather game-specific info. It's only a single table in the DB,
but the table is indexed by the field 'id'.
Is that your Primary Key? Does this ID have a meaning to you, or is it
random?
Whenever someone wants to add data to the table, they fill out a form, hit
submit and either get an error about wrong or missing data, or if ok get a
preview before being able to finally add it to the table. As the script is
now, it will attempt to add the data to the table, but if already there,
nothing happens, and there's no response to the user about it.
Do you mean if the ID is already there?
I want it to give a feedback message that the data is already in the
table,
and bump ppl back to the edit form with that message. For the other things
the script checks, all it does is load the variable $error with the
error-message and set a phase-flag to the right value, and then the page
generation part knows that there's been an error, and regenerates the
entry
form with the submitted data. So what I want, is to have the add data part
to just change the two variables needed for the error system to see
there's
something wrong, but I've got no idea what I need to change in the code to
do that. I've searched google and the archives, but come up with
nothing...
Here's the add-data part of the code...the variables in the comment is
just
so that I know what kind of data are in each variable, rather than what
they're actually set to in the DB:
elseif ($phase == 3) {
/*
num / id = int
country = string
nw = int
gov = string
strat = string
ally = string
spies = int
totdef = int
totoff = int
owner = string
*/
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die('Unable to select database');
$query = INSERT INTO ally2ally VALUES
('.$_POST['num'].','.$_POST['country'].','.$_POST['nw'].','.$_POST['gov'].','.$_POST['strat'].','.$_POST['spies'].','.$_POST['totdef'].','.$_POST['totoff'].','.$_POST['ally'].','.$_POST['owner'].');
mysql_query($query);
mysql_close();
}
the error message part is simply this:
?php
// if there is an error in the submitted data, display the error
message
if (isset($error)) {
?
table
tr
td?php echo($error); ?/td
/tr
/tablebr
br
?php
}
?
The rest of the code is not essential in this aspect. I just need some
help
to figure out how to make the script tell the user that it can't add the
data because the ID is already in the database...(that's the first field
btw).
I'm still not sure what exactly you want to do. But usually you make your
ID an AUTO_INCREMENT. That way you don't have to worry about giving the
same ID twice.
Regards,
Sebastian
TIA
Rene
--
Rene Brehmer
aka Metalbunny
http://metalbunny.net/
References, tools, and other useful stuff...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Once a problem is defined - it is half way solved. (Henry Ford)
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Importing CSV data
Hi there everyone, I have a database with email addresses from people who sign up for a newsletter on my website. Now, I have a utility i've written where they can save the addresses as a CSV file on their computer if they are logged in and the admin. What I would love to know, is how can I reserve this? What I mean is, if there is a CSV file with a single field (EMail) i'd like to be able to insert the data into the database from a file which would be selected from the admins computers. I can select a file from the computer no problem, what i'm not sure how to do is to go through the CSV file, and import the EMail addresses into a record of their own (IE: id1,id2,id3 rather than all in 1 row)). Any help / code tips would be REALLY appreciated :-) Regards Chris
Re: [PHP-DB] Importing CSV data
Chris Payne wrote: Hi there everyone, I have a database with email addresses from people who sign up for a newsletter on my website. Now, I have a utility i've written where they can save the addresses as a CSV file on their computer if they are logged in and the admin. What I would love to know, is how can I reserve this? What I mean is, if there is a CSV file with a single field (EMail) i'd like to be able to insert the data into the database from a file which would be selected from the admins computers. I can select a file from the computer no problem, what i'm not sure how to do is to go through the CSV file, and import the EMail addresses into a record of their own (IE: id1,id2,id3 rather than all in 1 row)). Any help / code tips would be REALLY appreciated :-) You can use fopen() to open the file and fgetcsv() to read one line at a time and parse for CSV fields. You could also just use file() and have an array full of the email address that you'd loop through and perform your INSERTs into the database. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: I need these 2 files
Thanks for your reply, everyone ! It turns out that when Apache 2 was installed with Redhat 9, the mod auth_mysql library was not installed. I followed everyone's advice and still did not get an error log - oddly enough. Well, in trying to cleanly remove Apache 2, in order to work with 1.3.x, for the sake of working with something a little better documented... Things got messy. Time to wipe and re-install, only because I don¹t want to spend more time on this than I already have - troubleshooting instead of working with PHP and MySQL. Sorry for the Off-Topic material, but you deserved a reply. Should be up and running sometime tomorrow Hello Roberto, Ref: My first little project, and search.html and results.php is not working in Redhat 9. == Pls, send me these two files so I like to run it to test myself. Thanks. LamSa From: Roberto Dragva Filho [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 23, 2003 5:20 AM Subject: [PHP-DB] Re: Failure to interact with MySQL in Redhat 9 ? Hello david, do you set register_globals to ON in /usr/local/lib/php.ini ? try it. Roberto David Haines [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hello all. I hope this is the best place to ask this (!) Problem: verified that PHP, MySQL and Apache are all working fine. My first little project, and search.html and results.php is not working in Redhat 9. If I fail to enter a search term, I get the expected error message, but that is just Apache, it's not even executing any PHP. When I enter a search term, the results.php page loads with the proper page title, but the rest of the page is blank. **However**, the exact same project files work just as expected on my Mac running OS X though... hmm: which is running Apache 1.3, PHP 4.1.2 and MySQL 4.1.2 So I have to wonder, what am I missing in Redhat ? What I've done, what I'm doing: I am learning MySQL and PHP, working through a lovely O'Reilly text. I have MySQL and PHP and Apache all working fine on an istall of Redhat Linux 9. I can work with MySQL in the terminal (command-line), and have even installed MySQL Control Center (a fairly nice GUI). My hello.php loads fine and returns Hellow world in my web-browser when I load http://localhost/hello.php The default Apache page loads, as do a simple index.html file when I put one in place. Apache 2 is running, PHP is version 4.2.2, MySQL is 4.0 I checked permissions and ownership of the search.html and results.php files. My results.php file is setup the same (ownership executable flags) as my hello.php file (which does work). My Apache error log offers nothing helpful. I cannot find any MySQL error log in Redhat (checked and triple-checked), and I can't seem to find any php error log. The php.ini file really doesn't help, in terms of knowing where to look for an error log === I know this is long, but I've been as thorough as I know how to be - please help ! (TIA) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
