Jump to content

smti

Members
  • Posts

    35
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

smti's Achievements

Member

Member (2/5)

0

Reputation

  1. smti

    Grouping Data

    Hello, I have what seems like a basic question, but I'm stumped. Here is my situation; I have a table called courses with the following fields: RID (The Record ID, Pri. Key) Subject CID (Course Section) Section Name Instructor Location StartTime EndTime Days What I want to do: I would like to generate a query which provides a list courses taught by each instructor and grouped by the instructor like so: Adams - Course A - Course B - Course C Jones - Course A - Course B - Course C ....And so on...... When I tried the following query: Select Instructor, Location, Days, StartTime, EndTime from Courses Group by Instructor; I do get some results, but not all the courses a particular instructor teaches are shown. Is there a better way to make this happen? Thanks for any insight!
  2. smti

    Data Import

    I also tried this with the same result: LOAD DATA LOCAL INFILE '/home/jalewellyn/tstProducts.csv' INTO TABLE tstProducts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (Name, Type, Brand, Description, ABV, TimesAvailable, Image, Website);
  3. smti

    Data Import

    Hello, I wrote a bash script to import some data from a CSV file. The code is as follows: Load Data local infile '/home/<USERNAME>/tstProducts.csv' into table tstProducts fields terminated by ',' enclosed by '"' lines terminated by '\n' (Name, Type, Brand, Description, ABV, TimesAvailable, Image, Website) Ignore 1 Lines; When I run the bash script I get the following error: syntax error near unexpected token `(' Any thoughts on why this may be occurring? I checked my code against a sample and everything looks correct. Any help or insight you can provide would be greatly appreciated! -smti
  4. Hello, I could use a little help solving the following problem: I have two tables: Counties and Products. The counties table contains a list of counties in which products are sold and the products table contains a list of products that are sold. I need to know the best way to write a query which produces a list products given the name of a county. My table structure structure is as follows: Table: Counties CountyID Name Table: ProductBrands BID Brand CountyID --> Foreign key relates to Counties.CountyID I tried using a join statement; however, I seem to get erroneous results. Any help would be greatly appreciated!
  5. The field: type_name does in fact exist in the SubProblemTypes table. Do you mean that a value must exist before the record can be created? -smti
  6. Hello - I am having a problem adding a new record to a table named: WizardChoices. When I execute the query I receive the following error: Cannot add or update a child row: a foreign key constraint fails (`resticket`.`WizardChoices`, CONSTRAINT `WizardChoices_ibfk_3` FOREIGN KEY (`sub_problem_type`) REFERENCES `SubProblemTypes` (`type_name`) ON DELETE SET NULL ON UPDATE NO ACTION) How can I correct this? Here is the structure of both tables; the relationship view is also attached. describe SubProblemTypes; +-----------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-------------+------+-----+---------+----------------+ | type_id | int(3) | NO | PRI | NULL | auto_increment | | type_name | varchar(25) | NO | MUL | NULL | | | assoc_primary_problem | int(3) | NO | MUL | NULL | | | type_description | varchar(25) | NO | | NULL | | +-----------------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> describe WizardChoices; +-----------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-------------+------+-----+---------+----------------+ | choice_id | int(3) | NO | PRI | NULL | auto_increment | | assoc_question_number | varchar(3) | NO | MUL | NULL | | | choice_text | text | NO | | NULL | | | primary_problem_type | varchar(50) | NO | MUL | NULL | | | sub_problem_type | varchar(50) | YES | MUL | NULL | | +-----------------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) Any assistance would be greatly appreciated! Thanks!
  7. smti

    SQL Join

    Hi Everyone: The Situation: I am working on a web-based questionnaire project. I have two tables: WizardQuestions and WizardChoices -- WizardQuestions stores the questions themselves, while the WizardChoices stores the possible answers associated with each question. Database Schema: WizardQuestions has the following fields: 1. question_id 2. question_header 3. question_body WizardChoices has the following fields: 1. choice_id 2. assoc_question_id (Relates to table above) 3. choice_text The Problem: Using one query, I need to get: 1. question_header from WizardQuestions 2. question_body from WizardQuestions 3. choice_text from WizardChoices associated with that question. ** Header and question body should be returned once ** I have tried: The inter-select: Select wizard_questions.question_id, wizard_questions.question_header, wizard_questions.question_body FROM wizard_questions WHERE wizard_questions.question_id = (Select wizard_question_choices.choice_text from wizard_question_choices where wizard_questions.question_id = wizard_question_choices.assoc_question_id); The JOIN: SELECT WizardQuestions.question_header, WizardQuestions.question_body, WizardChoices.choice_text FROM WizardQuestions JOIN WizardChoices ON WizardQuestions.question_id = WizardChoices.assoc_question_id; I have had very little success. If I run the join statement without a GROUP BY statement, then the question_header and question_body are shown twice -- with both choice options, but if I add a GROUP BY statement, then the header and body are shown, but with one option only. Any help would be greatly appreciated!
  8. Hello, I am a bit stumped as to how I can combine a select and select count statement together. I need to select fields from one table and count the number of records in another table using one of the fields from the first table's results. My table structure is as follows: Trackchasers contains the following fields: 1. cid 2. cfn 3. ccitty 4. rgid 5. ctid Both the trackchasers and trackvisits tables share the cid field. I am able to execute queries individually to get some of the data I need, however, I need to combine these queries if possible so it is easier to work with in PHP. My selection query: select cid, cfn, cln, ccity, rgid, ctid from trackchasers My count query: select count(cid) as trackvisits from trackvisits where cid=12; How can I combine these two statements to display all the fields I need plus the counting code? I tried using an inter join query, but had little luck. Thanks for any help you can provide! -smti
  9. Hello - After reviewing the advice provided, I have added the error reporting code to the top of both my migration_logic and queue_intel files; I receive one notice, but no errors. I have also changed the include statement to a require and everything appears to work -- the file is being found because the require statement is not outputting an error. FYI: The notice I receive is: Notice: Undefined index: liability in /var/www/rescomp/newresticket/pages/studentfrontend/scripts/migration_logic.php on line 24 This is from a posted variable not related to my problem. Any other thoughts? Thanks, -Smti
  10. Hi Folks, I am writing an PHP application that utilizes OOP. At the moment I have run in to a very simple problem I cannot seem to solve. Here is my situation: I have two independent files -- one file called migration_logic.php and another named queue_intel.php Inside the migration logic is a function which relies on a method inside the queue_intel file. It appears that the function inside the migration_logic file is unable to call the method inside the queue_intel.php file and I am not sure why. Here is my code: Migration_logic.php Function Add_To_Queue(){ Echo "Inside migration logic add to queue function!"; Include("../../../common/queue_intel.php"); $assign = new Queue_intel; $assign->assign_to_queue(); } Queue_Intel.php Class Queue_intel{ function assign_to_queue(){ /** Step 1: Determine from which location the student filed their request. Note: Queue assignment ONLY occurs if the student filed from within an RCHD office location, otherwise queue assignment occurs at ticket conversion time! */ $student_submission_location = $_SESSION['student_submission_location']; if($student_submission_location==0){ //This student is filing from outside and RCHD office! $_SESSION['queueposition'] = 0; } if($student_submission_location==1){ //This student is filing from inside and RCHD location so we need to add them to the proper queue! /** Steps for queue assignment inside an RCHD location: 1. Determine the RCHD location the student is currently visiting by capturing the answer to their question. 2. Count the number of tickets currently active for the desk the student is currently visiting! 3. Assign the student to the queue for the particular RCHD location they are visting */ $rchd_location = $_SESSION['rchd_location']; // <-- This grabs the current location of the student. /** Now we need to count the number of tickets currently in the queue add the new ticket to the bottom of the queue */ Include('../includes/connection.inc.php'); $num_tickets_in_queue = pg_query($dbh, "select * from v2_office_tickets where ticket_status='In Queue' and desk_location = '$rchd_location'"); $num_tickets_in_queue = pg_num_rows($num_tickets_in_queue); //Augment the current queue value and then assign it to variable to put in to the DB. $initial_queue_position=$num_tickets_in_queue+1; //Setup a session variable to pass the queue position to the ticket confirmation form. $_SESSION['queueposition'] = $initial_queue_position; } // End Assign Queue Function } } // End Class Function In short, the function add_to_queue inside the migration_logic.php file needs to be able to be able to call the method assign_to_queue located in the queue_intel.php file. Any help would be greatly appreciated! -Smti
  11. Hi Folks, I am having a problem with a search function I am writing. Here is the situation: I have text box named "search_for" where users can enter the term they want to find in a particular record. Users must then select from a drop down what field in the database they would like to check the term for. Users can select the following fields in the database to search: 1. ticketid 2. name 3. username 4. building Searching for a particular ticket ID appears to work fine, however, searching for anything else in any one of the other database fields produces an error. For example, if I search for "Columbia" and select building from the drop down I get the error: Error in SQL query: ERROR: column "Columbia" does not exist. Here is my search query code (and code to show result: include ("../../../../includes/connection.inc.php"); // execute query $sql = "SELECT ticketid, opendate, name, status FROM tickets where $in_what=$search_for order by ticketid asc"; $result = pg_query($dbh, $sql); if (!$result) { die("Error in SQL query: " . pg_last_error()); } // iterate over result set // print each row //while ($row = pg_fetch_array($result)) { $count=0; echo '<tbody>'; // keeps getting the next row until there are no more to get while ($row = pg_fetch_array($result)) { // Print out the contents of each row into a table $classStr = $count++ % 2 ? '' : "class='alt'"; echo "<tr $classStr> <td> $row[0]</td> <td> $row[1]</td> <td> $row[2]</td> <td> $row[3]</td>"; //Grab ID and put in variable. $id=$row['ticketid']; echo "<td><a href='viewticket.php?id=$id'><img src='images/icons/view.png' alt='View Record'></a></td> </tr>"; } echo '</tbody>'; // close connection pg_close($dbh); } Why can I locate records using the ticketid field, but not the others? Any help would be greatly appreciated! Thank You, smti
  12. smti

    Postgres Update

    Thanks.... I got it working.
  13. Hi Folks, I am designing a work ticket system for my office. Right now, those who file a work ticket need to know where they are in the work queue. To accomplish this, I have created a field called pqv (Present Queue Value). When the person enters a work ticket, they are assigned their queue value. Now the problem: Once a ticket is closed, the PQVs for all the other open tickets need to be adjusted accordingly (By subtracting 1 from their PQV). I have written a query to display all open records and their corresponding queue values; however is displayed and updated, not all of them. Here is my code: function adjust_queue(){ include("../../../includes/connection.inc.php"); //Get Queue Values for those still in the database. $sql = "SELECT ticketid, pqv FROM tickets where status = 'In Queue'"; $result = pg_query($dbh, $sql); if (!$result) { die("Error in SQL query: " . pg_last_error()); } // iterate over result set // print each row while ($row = pg_fetch_array($result)) { echo "Ticket ids are:" .$row[ticketid]; echo "<br>"; echo "Queue Values are:" .$row[pqv]; echo "<br>"; //Subtract one from present queue value of each student in queue. $present_queue_value=$row['pqv']; $new_queue_value = $present_queue_value-1; //Excute update query with new queue value $ticketid=$row['ticketid']; $query = "UPDATE tickets SET pqv='$new_queue_value' where ticketid='$ticketid'"; } } //Send Confirmations to student send_closing_confirmation(); } When the TicketIDs and PQVs are echo'd only one record is displayed. It seems that if I remove the update query and only display open records all records in the database are show properly. Any help would be greatly appreciated! Thank You, smti
  14. Hi, I need help pulling the value produced from an SQL query. I will explain, but here is the code: <? include("includes/calendarconnection.inc.php"); ?> <? $today=date("m.d.y"); $result = mysql_query("SELECT * FROM $tbl_name where event_date=curdate()"); $eventstoday = mysql_num_rows($result); echo $eventstoday; echo "<br>"; if ($eventstoday==0){ echo "No events today, execute query for date in two days"; $newdate = mysql_query("Select date_add(curdate(), interval 2 day"); echo $newdate; } else { // Retrieve data from database $sql="SELECT * FROM $tbl_name where event_date=curdate() limit 1"; $result=mysql_query($sql); // Start looping rows in mysql database. while($rows=mysql_fetch_array($result)){ echo $rows ['event_date']; echo "<br>"; echo $rows ['event_title']; echo "<br>"; echo $rows ['event_desc']; } // close while loop } // close connection mysql_close(); Essentially, I need help with the IF statement: if ($eventstoday==0){ echo "No events today, execute query for date in two days"; $newdate = mysql_query("Select date_add(curdate(), interval 2 day"); echo $newdate; } I need to execute the new date query and then echo the date provided by the query. I tried just performing the query and then echoing, but this does not seem to work. Do I need to add a "fetch row query"? Any help you can provide would be much appreciated. Thank You, smti
  15. Hi, I am currently working on a script where I have to determine the current date and then add five days to the current date. I have tried using the experimental Date_Add function, but have not had any luck making it work. Does anyone have any idea how I can achieve my goal? I need to add five days to the current date and then use the outputted date in a MYSQL query. Here is what I have thus far: $currentdate=date("m.d.Y"); Thank You, smti
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.