PHP - Exporting Multiple Csvs From Multiple Tables

I hope this isn't a stupid question.  I have written a fairly large site using php and SQL.  The site has multiple features (how tos, classifieds, photos, videos, ect) with each section's data housed in its own table.  Now I would like to have one highlight from each section appear in boxes on the index page simutaniously. (basically returning one random item from each table when the page is loaded)  I know how to call each table individually with sql = mysql_query, however, I fear that calling the database that many times over and over in one page load could be problematic if the site begins to get a lot of traffic.   Am I correct in worring about overloading the server and is there a better way to do this?

Hi All,

I am adding a button that will delete many things in the system which all have the same $job_id

There is going to be 10 or so tables that need to delete *  where $job_id = ?

Is there a better way to do this rather than just iterating my code 10 times.

I was looking at just joining but isnt one benefit of prepared statements that they can be used again and again to increase speed.

I'm having a hard time grasping how to select data from several tables.

Here's the code

Code: [Select]
<?php 
require_once "config.php";
$gamedate = $_SESSION['date'];
echo "These umpires are not currently scheduled on this date, and have not asked for the day off:<br />";

$umpirelist = Array('umpire 1 name', 'umpire 2 name', 'umpire 3 name');

$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);  

foreach($umpirelist as $data) { 

//now get stuff from a table           
$sql = "SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire FROM umps, calendar, vacation WHERE umps.full_name = $data AND $gamedate = `calendar.date` AND $gamedate = `vacation.date` AND $data NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND $data NOT IN ('vacation.umpire') order by umps.full_name asc"; 

$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 
echo "$row[$data]<br />";
}
}
?>

The table CALENDAR holds the date of the game, who is playing, and what umpires are schedule for that game.  it does have a unique "row_number" column as well, but didn't think I'd need it in this select.

The table VACATION has the date, and umpire's name that is on vacation.

The table UMPS contains the umpires name (same names as the array in the code) and email addresses and login info.

I'm getting this error:

Quote

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 152



This is line 152

Quote

while ($row = mysql_fetch_assoc($rs))  {



Can someone please point me in the right direction as to where I'm going wrong?

Thanks!

 
I'm not sure if this belongs in the PHP or SQL sections or both but-
Is it possible to insert form data (one record) into 2  different tables when I click submit?
  As is, I have 2 distinct log-in scripts each linked to 2 separate databases .  I want to merge them into one log-in form that populates both tables(in different dbs). I don't want my visitors to log into 2 different forms.  Can one form's action attribute send the POST data to two different processing scripts? and can the input from a text  box be sent to 2 different fields?
I don't have any code, I just wanted to know if it can be done or point me to a sample that I can work from.

Hi,

I'm very new to PHP and have managed to get through most of the problems I've encountered so far, but I have got to one point that I can't seem to solve.

I have two mysql tables (Categories & Products), I have a page that shows the categories (generated from the categories table) and when a category is selected it lists the products in that category.

Due to stock etc. not all categories always have products associated to them.

The Category & Product Tables are automatically updated, so what I need to do is only show categories that have products associated to them, both tables have a column category_no, and nothing I've tried so far seems to work.

The code I have so far is:-

Code: [Select]
Require_once('config.php');

Mysql_connect(db_host, db_user, db_password);

@mysql_select_db(db_database) or die( "unable to select database");

$query="select * from categories";

$result=mysql_query($query);

$num=mysql_numrows($result);

Mysql_close();
Thanks

Pete

Need help in updating the tables

I have array of data coming from a form which I'm inserting in my DB. I have 5 tables
product
filter
product_filter
heater
product_heater

Im able to put the data in the "product", "filter" & "heater" tables but i dont know how to put data inside the "product_filter" & "product_heater" table. Any help or direction to any tutorails is appreciated.

My Tables structu

product
id int(5)
product text
cost text
details text

filter
id int(5)
filter text
imgpath text

product_filter
id int(5)
id_product int(5)
id_filter int(5)

heater
id int(5)
heater text
imgpath text

product_heater
id int(5)
id_product int(5)
id_heater int(5)


Code: [Select]
// Product data Update
$name = mysql_real_escape_string($_POST['product']);
$cost = mysql_real_escape_string($_POST['cost']);
$details = mysql_real_escape_string($_POST['details']);

$sql_title = "INSERT INTO product (
id ,
product ,
cost ,
details ,
)
VALUES (
NULL , '$name' , '$cost' , '$details')";
if (!mysql_query($sql_title,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records for product added<br />";


// Filter update
// This is the array which is coming from the form
/*
Array ( [0] => ehiem
[1] => Hagan
[2] => Rena
[3] => jobo )


Array ( [0] => img1.jpg
[1] => img2.jpg
[2] => img3.jpg
[3] => img4.jpg )
*/

$filtername = mysql_real_escape_string($filtername);
$filterimgpath = mysql_real_escape_string($filterimg);
$combined_array = array_combine($filtername, $filterimgpath);
$values = array();
foreach ($combined_array as $filtername => $filterimgpath)
{
    $values[] = "('$filtername', '$filterimgpath')";
}
$sql = "INSERT INTO filter (filter , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product Filter Update table
// This is where Im stuck. Not able to even think of anything....


// heater update
// This is the array which is coming from the form
/*
Array ( [0] => ehiem
[1] => Dolphin
[2] => Rena
[3] => jobo )


Array ( [0] => img1.jpg
[1] => img2.jpg
[2] => img3.jpg
[3] => img4.jpg )
*/

$heatername = mysql_real_escape_string($heatername);
$heaterimgpath = mysql_real_escape_string($heaterimg);
$combined_array = array_combine($heatername, $heaterimgpath);
$values = array();
foreach ($combined_array as $heatername => $heaterimgpath)
{
    $values[] = "('$heatername', '$heaterimgpath')";
}
$sql = "INSERT INTO heater (heater , imgpath) VALUES " . implode(', ', $values);
//echo $lastid = mysql_insert_id()."<br />";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "records added<br />";

//Product heater Update table
// This is where Im stuck. Not able to even think of anything....

How do I search through table 1, 2, 3 where a=x and b=y and c=z?

Hey,

I have a query running inner joins... which currently works, but I need some more information pulled from other tables...

Code: [Select]
define('WPSC_TABLE_PRODUCT_LIST', "{$wp_table_prefix}wpsc_product_list");
define('WPSC_TABLE_PRODUCTMETA', "{$wp_table_prefix}wpsc_productmeta");
define('WPSC_TABLE_CATREF', "{$wp_table_prefix}wpsc_item_category_assoc");
define('WPSC_TABLE_CATNAME', "{$wp_table_prefix}wpsc_product_categories");


$cf="Linked Products";
$sku = get_post_meta($post->ID, $cf, true);
$array = explode(",",$sku);

$products = array_count_values($array);

foreach($products as $key => $value) {
$product = $wpdb->get_row("SELECT meta.product_id AS pid, list.name AS name, list.price AS price, retailer.category_id AS catid FROM ".WPSC_TABLE_PRODUCTMETA." AS meta INNER JOIN ".WPSC_TABLE_PRODUCT_LIST." AS list ON ( list.id = meta.product_id ) INNER JOIN ".WPSC_TABLE_SHOPNAME." AS retailer ON ( retailer.product_id = meta.product_id ) WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC");
?>
But I also need the category name - So I have to cross reference the following...

wordpdem_wpsc_product_list

id      name
433   itemone
432   itemtwo
431   itemthree

wordpdem_wpsc_item_category_assoc

id   product_id    category_id
1    433                    1
2    432                    2
3    410                    3

wordpdem_wpsc_product_categories

id  name
1   Bread
2   Fish
3   Snacks

I've now written:
Code: [Select]
SELECT
meta.product_id AS pid,
list.name AS name,
list.price AS price,
retailer.category_id AS catid,
category.name AS catname

FROM wordpdem_wpsc_productmeta AS meta
INNER JOIN wordpdem_wpsc_product_list AS list ON ( list.id = meta.product_id )
INNER JOIN wordpdem_wpsc_item_category_assoc AS retailer ON ( retailer.product_id = meta.product_id )
INNER JOIN `wordpdem_wpsc_product_categories``wordpdem_wpsc_product_list` AS category ON ( retailer.category_id = category.id )
WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC
However it fails on line 12 (the Where statement...)

Any ideas what I've done wrong?

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' users.username='$username'");


How do I get it so it selects data from the table tickets where id='$existing', but I also want to get a user's (who is viewing the ID) position (rank). $username is a session. So, any thoughts on how I would do so?

My updated code:


<?php
session_start();
include("../includes/mysql.php");
include("../includes/config.php");

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="../style.css" rel="stylesheet" type="text/css" />
<title><?php echo $title; ?></title>
</head>

<body>
<div id="container">
<div id="content">
<div id="left">
<div class="menu">
<?php include("../includes/navigation.php"); ?>
<div class="menufooter"></div>
</div>
<?php include("../includes/menu.php"); ?>
</div>
<div id="middle">
<?php
$existing = $_POST['existing'];
$ip = $_SERVER['REMOTE_ADDR'];
$username = $_SESSION['user'];

$query = mysql_query("SELECT COUNT(tickets.id),tickets.id,tickets.status,tickets.date,tickets.question,tickets.title,users.position FROM tickets,users WHERE tickets.id='$existing' OR users.username='$username'");
$get = mysql_fetch_assoc($query);

if(!$existing)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>You have not enetered in a ticket ID. Please go back and do so.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['COUNT(id)'] < 1)
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>The ticket ID you are trying to use doesnt exist. Please go back or submit another ticket.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
elseif($get['tickets.ip']==$ip || $get['user.position'] >= 1)
{
$status["tickets.status"]["0"] = "Waiting for support...";
$status["tickets.status"]["1"] = "Waiting for user...";
$status["tickets.status"]["2"] = "Ticket Closed...";
$status["tickets.status"]["3"] = "Ticket Opened...";

echo 
'
<div class="post">
<div class="postheader"><h1>View Ticket Status - ID '. $get["id"] .'</h1></div>
<div class="postcontent">
<p>Title: '. $get["tickets.title"] .' - Posted on: '. $get["tickets.date"] .'</p>
<p>Ticket Status: '. $status[$get["tickets.status"]] .'</p>
<p>Question: '. nl2br($get["tickets.question"]) .'</p>
</div>
<div class="postfooter"></div>
</div>
';

}
else
{
echo 
'
<div class="post">
<div class="postheader"><h1>Error</h1></div>
<div class="postcontent">
<p>This is not your ticket. You only have permission to view your tickets. Please go back.</p>
</div>
<div class="postfooter"></div>
</div>
';
}
?>
</div>
</div>
</div>
</body>
</html>

I have been learning php and have put together a simple site to track vehicle faults and repairs, but now i am trying to transfer it to cakephp framework to make it easier to work with. My problem is that i have 4 tables, the fault and repair a list of vehicles a list of symptoms a list of fault codes. Each repair can have 3 vehicles, 3 symptoms and 3 codes relating to it so i have lookup table to link the fault/repair id to the symptoms, code and vehicles. i have setup basic code for the vehicle , symptom and fault code models like this: public $hasAndBelongsToMany = array(
'Fault' =>
array(
'className' => 'Fault',
'joinTable' => 'Faults_vehicles',
'foreignKey' => 'vehicle_id',
'associationForeignKey' => 'fault_id'
) 
); and this lets me search for any faults in the faults table with a given vehicle ID, and this works fine. My problem is how can i set it up so i can do a search for faults in the Faults table with a given vehicle ID and a given symptom ID that have a matching Fault code ID. I am very new to php and have only just started using cakephp so sorry in advance if this makes no sense at all. thanks for your time j  

Hi, 
I have multiple table 
Table -1 
order_no name 
1 raj 

table -2 
order_no name 
1 raj 

table 3 
order_no name 
1 raj 

table 4 
order_no name 
1 raj 

table 5 
order_no name 
1 raj 

I want a query to check if the id (one) is present in all tables or not, 
i create this. 


select order_no from prepress, press, postpress, qc, binding, dispatch where order_no=prepress.order_no AND order_no=press.order_no AND order_no=postpress.order_no AND order_no=qc.order_no AND order_no=binding.order_no AND order_no=dispatch.order_no 
but the ambiguous error for order_no. 

how to achieve this can anyone suggest me? 
thanks

Hello

1) I am reading two good tutorials for building a search page with pagination:

http://www.phpfreaks.com/tutorial/basic-pagination
http://php.about.com/od/phpwithmysql/ss/php_pagination.htm
Before I ask the big question, I would like to know how does a query look like when searching multiple tables (e.g three) in the same time.
Table1/Table2/Table3.

May you kindly give me example?

2) The problem is that I have multiple tables with the same structure to search e.g. (title, body)
a) how I will know the ROWS NUMBER returned by the search query? it doesn't look a smart way to query each table and them count the results, and this basically takes much time right?
b) when displaying the result, how I will be able to set the correct hyperlink for each result?
you know, each table is a different subject, each one is located in different folder.

Thank you SO MUCH

I have 5 tables that I changed on my development server (laptop) that I'd like to upload to my test server. I can export just those tables from PhpMyAdmin but on import it drops all of the other tables first. Short of editing the SQL before I upload it (finding the drop and deleting it) is there an easy way to uncheck a box and change that behavior that I am just not seeing?

Ok I have a fairly straight forward question. I am designing a baseball website and I am trying to add in a schedule, here is what I have:

Data Bases
1) Teams DB - Each team has a unique team ID, location, logo, and owner
2) Schedule DB - Each game has a unique game ID, and the schedule is set up like this: AWAY = Team ID, HOME = Team ID....

So I assume I am using the JOIN command to pull the schedule from both databases, any idea how the SQL command would look?