PHP - Moved: Query Matching Multiple Columns In Multiple Rows??? (serialized Data?)

Not sure if this is the right forum, but I have a database that I will need to populate with a large number of rows (2000+).

I have written a PHP script that uploads individual entries.

Is it possible to use something like a spreadsheet where I can set out the rows/columns as they will appear in the database, and then upload in one go rather than uploading each row individually?

Thanks for any observations and/or help.

Hi, I need help to check multiple rows for many columns, so that the script won't re-insert a duplicate entry. For eg:


$isRowExist=mysql_query("SELECT (field1,field2) from table1 WHERE field1='$f1' AND field2='$f2' ");

if(mysql_num_rows($isRowExist)==1)
   print "already in table1!";
else
   //proceed to insert into table table1


*BUT I tried this and it says mysql_num_rows expects resource, and this warning only shows when I have multipel columns I want to compare (it works just fine with one column in the WHERE clause above)

Please help!

Hi there,

This is what I need to do, blow that is the code I have knocked up to do it but it doesn't seem to work. The main problem is the query that matches the atc callsigns to the pilots destinations.. can't fathom it myself.

1. Pull all the records from my database.
2. Assign their unique user id's, callsigns and destinations.
3. Execute a query in which you: SELECT * FROM CLIENTS WHERE (The 'clienttype' = 'ATC') AND match the callsign of the ATC users to the 'planned_destairport' of the 'clienttype' = 'PILOT'.

For example if three people are going to Heathrow, then it would return 3 results.
4. Then do a mysql_num_rows of the result.
5. Lastly, execute a query that would update the 'atcarrivals' collumn in the database with the count number using the unique 'cid' as the identifier.

All in a loop of course.


// Selects all of the ATC Records.
$atc_arrivals_selectquery = mysql_query ("SELECT * FROM CLIENTS");

// Starts the loop for fetching the records of the previous query.
while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery)){

// Sets the users cid, planned_destairport and callsign.
$atc_cid = $atc_arrivals_row['cid'];
$atc_destinations = $atc_arrivals_row['planned_destaiport'];
$atc_callysign = $atc_arrivals_row['callsign'];

// Searches the database and selects all records where the callsign is LIKE the destination airport.
$atc_arrivals_check = mysql_query("SELECT * FROM CLIENTS WHERE clienttype = 'ATC' AND callsign LIKE '%$atc_destinations'");

// Counts previous query results.
$atc_arrivals_count = mysql_num_rows($atc_arrivals_check);

// Updates the atcarrivals column with the count..
$atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'");

// End loop.
}


Concerned columns are these:
"cid" - Unique client id.
"atcarrivals" - Number of arrivals from count. (This is the problem.)
"planned_destairport" - Destination airport of pilot. Usually four letters long. IE. EGLL = Heathrow.

Thanks.

I made a small editing system for my news page, and I need to update three columns within my table "announcements" in the database. I tried a method of updating all of them with one MySQL query instead of using three as it just isn't neat. I've searched several methods via google and I've tried all of them, but just can't seem to get it to work. Is this MySQL query correct?


mysql_query("UPDATE announcements SET title = {$title} WHERE id = '$id', content = {$content} WHERE id = '$id', lastmodified = ". date('M-d-Y') ." WHERE id = '$id'");

I am loading notifications from a database table called "notifications" and I am having a little trouble getting them to order in the correct way.

my query I'm using right now:

Code: [Select]
$query = mysql_query("SELECT B.* FROM (SELECT A.* FROM notifications A WHERE A.user_id='$session' AND A.from_id!='$session' ORDER BY A.id ASC ) AS B ORDER BY B.state ASC LIMIT 7");

this works well as far as showing the unread notifications on top, then the read notifications below, however it's not ordering the two sets by ID (which the id auto increments so the higher id number is the newest) from newest on top to the oldest on bottom, still keeping them separated by the unread on top, read on bottom (column name is state for showing whether they're read or not).
The order the notifications are displaying by their ID is:

3
5
2
4
1

when it should be:

5
3
2
4
1

Hi, after banging my head against the wall for a while thinking this would be a simple task, I'm discovering that this is more complicated than I thought.

Basically what I have is a link table linking together source_id and subject_id.   For each subject there are multiple sources associated with each.

I had created a basic listing of sources by subject... no problem.   I now need a way of having a form to create an ordered list in a user-specified way.  In other words, I can currently order by id or alphabetically (subject name lives on a different table), but I need the option of choosing the order in which they display.  I added another row to this table called order_by.  No problem again, and I can manage all of this in the database, however I want to create a basic form where I can view sources by subject and then enter a number that I can use for sorting.

I started off looping through each of the entries and the database (with a where), and creating a foreach like so (with the subject_id being grabbed via GET from the URL on a previous script)
Code: [Select]
while($row = mysqli_fetch_array($rs)) {


//update row order
if (isset($_POST['submit']))  {

//get variables, and assign order

$subject_id = $_GET['subject_id'];
$order_by = $_POST['order_by'];
$source_id = $row['source_id'];


//echo 'Order by entered as ' . $order_by . '<br />';

foreach ($_POST['order_by'] as $order_by) {

$qorder = "UPDATE source_subject set order_by = '$order_by'
WHERE source_id = '$source_id'
AND subject_id = '$subject_id'";

mysqli_query($dbc, $qorder)
or die ('could not insert order');

// echo $subject_id . ', ' . $order_by . ', ' . $source_id;
// echo '<br />';
} 
}
else {
$subject_id = $_GET['subject_id'];
$order_by = $row['order_by'];
$source_id = $row['source_id'];

}

And have the line in the form like so:
Code: [Select]
echo '<input type="text" id="order_by" name="order_by[]" size="1" value="'. $order_by .'"/>

(yes I know I didn't escape the input field... it's all stored in an htaccess protected directory;  I will clean it up later once I get it to work)

This, of course, results in every source_id getting the same "order_by" no matter what I put into each field.

I'm thinking that I need to do some sort of foreach where I go through foreach source_id and have it update the "order_by" field for each one, but I must admit I'm not sure how to go about this (the flaws of being self-taught I suppose;  I don't have anyone to go to on this).

I'm hoping someone here can help?

Thanks a ton in advance

Hey, I was wondering if there is a way to pull multiple rows at once using a list of unique identifiers. For example, I want to pull the rows with the IDs of 4,13,91 and 252

I know the WHERE part of this query is incorrect, but I'm putting it to hopefully help you guys understand what I'm looking for.

$result = mysql_query("SELECT * FROM $table WHERE id='4' OR '13' OR '91' OR '252'");
while($row = mysql_fetch_array($result))
{
echo($row['name']);
}


Or is the best way simply to do it one query at a time without a while statement? There could be as many as a few dozen records being pulled per page.

I insert data in mysql table row using multiple method : 1#2#3 . 1 is ID of country, 2 is Id of state, 3 is ID of town . now i have this table for real estate listings. for each list(home) i have country/state/town (1#2#3). in country table i have list of country - in country table i have list of state - in country table i have list of town. i need to The number of houses in country / state / town . my mean is :
Code: [Select]
USA [ 13 ] <!-- This Is equal of alabama+alaska+arizona -->
----Alabama [8] <!-- This Is equal of Adamsville+Addison+Akron -->
-------Adamsville [2]
-------Addison[5]
-------Akron[1]
......(list of other City)
----Alaska [ 3 ]
-------Avondale[3]
......(list of other City)
----Arizona [ 2 ]
-------College[2]
......(list of other City)
Lisintg Table :

Code: [Select]
ID -- NAME -- LOCATION -- DATEJOIN -- ACTIVE
 1 -- TEST -- 1#2#3    -- 20110101 -- 1
 2 -- TEST1 -- 1#2#3    -- 20110101 -- 1
 3 -- TEST2 -- 1#3#5    -- 20110101 -- 1
 4 -- TEST3 -- 1#7#6    -- 20110101 -- 1
Country Table :

Code: [Select]
id       -- name 
 1        -- USA 
stats Table :

Code: [Select]
id        -- countryid -- name 
 1        -- 1             -- albama
 2        -- 1             -- alaska
 3        -- 1             -- akron
town Table :
Code: [Select]
id        -- countryid -- statsid -- name 
 1        -- 1             -- 1         -- adamsville
 2        -- 1             -- 1         -- addison
 3        -- 1             -- 1         -- akron
Thanks For Any Help.

right now it displays the first column of the array as a header for each foreach loop then i've got the data displayed under each header and it has 4 columns or a number of my choosing with data displayed under the Table Headings. I am trying to make it display 4 or so columns but with the each group having a certain number of grouped table (data/header) in each column. a different amount for each column that i can choose

IF Possible i'd like the choice of being able to remove the headers eg. Z when there is no data in them.

This is an example of how it is being displayed right now

Any Help would be greatly appreciated

Quote

A
Airlie Beach   Andergrove   Alexandra   Armstrong Beach
Alligator Creek
B
Bucasia   Blacks Beach   Beaconsfield   Bakers Creek
Balberra   Bloomsbury   Breadalbane   Ball Bay
Belmunda
C
Cannonvale   Calen   Crystal Brook   Cremorne
Chelona   Campwin Beach   Cape Hillsborough
Conway


Heres The Code i have so far.

Code: [Select]
<?php
  $file = "widget.csv";
@$fp = fopen($file, "r") or die("Could not open file for reading");
$outputArray = array();
$count = 0;

while(($shop = fgetcsv($fp, 1000, ",")) !== FALSE)
{
    $outputArray[array_shift($shop)] =  array_filter($shop);
}

echo "<table>";
foreach ($outputArray as $alphabetical=>$value){
    echo "<th colspan='4'><b>" . $alphabetical ."</b></th>";
    echo "<tr>";
foreach ($value as $key){
$afterkey=preg_replace('/\s+/','-',$key);
    if ($count == 4){
    echo '</tr><tr><td><a href="map.php?mapaddress='.$afterkey.'-qld&mapname='.$key.'">'.$key.'</a></td>';
    $count = 0;
    }else{
    echo '<td><a href="map.php?mapaddress='.$afterkey.'-qld&mapname='.$key.'">'.$key.'</a></td>';
    }
$count++;    
}
    echo "</tr>";
$count = 0;
}
    echo "</table>";

    echo "\n<br />";

?>


Heres an example of what i am trying to do.

A D K N S AlexandraHeadlands DickyBeach KingsBeach Nambour SandstonePoint Aroona Diddillibah KielMountain Ninderry ShellyBeach Doonan KundaPark NoosaHeads SippyDowns B Dulong Kuluin Ningi SunriseBeach Beachmere DeceptionBay Kilcoy NorthArm SunshineBeach BanksiaBeach Noosaville Scarborough Beerburrum E L Beerwah EerwahVale Landsborough O T Bellara Elimbah Tanawha Bellmere Eudlo M P TowenMountain Birtinya Eumundi Maleny Petrie Tewantin Bongaree Mapleton Palmview TwinWaters Bokarina F Marcoola Palmwoods BribieIslandArea Flaxton MarcusBeach Parklands U Buddina ForestGlen MaroochyRiver Parrearra UpperCaboolture Burnside Maroochydore PeregianBeach Buderim G Minyama Pinbarren V Burpengary GlassHouseMountains MoffatBeach PointArkwright Valdora BliBli Mons PelicanWaters H Montville PacificParadise W C Highworth Mooloolaba WeybaDowns CoolumBeach Hunchy Mooloolah Q Warana Caboolture MountainCreek WestWoombye CabooltureSouth I MountCoolum R Woombye Caloundra ImageFlat Morayfield Rosemount Woorim CastawaysBeach Mudjimba Redcliffe WamuranBasin Chevallum J Woodford CoesCreek WoodyPoint Cooroy Wamuran Currimundi Wurtulla X Y YandinaCreek Yandina Z

Given the below 3 database tables I am trying to construct a SQL query that will give me the following result:

customer_favourites.cust_id
customer_favourites.prod_id OR product.id
product.code
product.product_name
product.hidden
product_ section.section_id (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.catpage (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)
product_ section.relative_order (MUST BE ONLY THE ROW WITH THE LOWEST SECTION ID, I.E. ROW ID #44108)

I currently have....

SELECT customer_favourites.cust_id, customer_favourites.prod_id, product.code, product.product_name, product.hidden, product_section.section_id, product_section.relative_order, product_section.catpage  
FROM customer _favourites  
INNER JOIN product ON customer_favourites.prod_id = product.id  
INNER JOIN product_section ON product_section.product_code = product.code  
WHERE `cust_id` = '17' AND `hidden` = '0' GROUP BY `code`  
ORDER BY `section_id` ASC, `relative_order` ASC, `catpage` ASC LIMIT 0,30

This gives me what I want but only sometimes, at other times it randomly selects any row from the product_section table.

I was hoping that by having the row I want as the last row (most recent added) in the product_section table then it would select that row by default but it is not consistent.

Somehow, I need to be able to specify which row to return in the product_section table, it needs to be the row with the lowest section_id value or it should by the last row (most recent).

Pulling my hair out so any help is gratefully received.  
customer_favourites

id     cust_id   prod_id
70    4            469
product

id        code           product_name    hidden
469    ABC123     My Product        0
product_section

id                  section_id      catpage    product_code    relative_order     recommended
44105         19                   232            ABC123             260                    1
44106          3                     125            ABC123              87                      1
44107          2                     98              ABC123             128                     1
44108          1                    156             ABC123             58                        0

 

I'm trying to write a php page that displays data from a JOIN query for a specific ID

table view

brandinfo

ID, brand, discounttype

1, antioni, no discount

brandproducts

brandID, producttype, price

1, Tshirt, 20.00
1, Pants, 30.00
1, Shoe, 40.00


the returned result is

1 antioni, no discount, Tshirt, 20.00,
2 antioni, no discount, Pants, 30.00
3 antioni, no discount, Shoe 40.00

The way I want the page to be displayed is

------------------


Antioni (at the top)

Table

1. Tshirt 20.00
2. Pants 30.00
3. Shoe 40.00


no discount (at the bottom)
----------------------------

How should I construct the PHP page from the result since they're retrieved as rows?

Hi Guys

Just need some advice to go in the right direction.

I'm working on a csv upload script (part of a bigger thing i'm building), so i read in the csv to a multipdimensional array and then build a query that inputs all rows in one query - i read this is the most efficient way to import multiple rows of data at once(rather than multiple insert statements). Just for illustration here's the code i use to build the query so you understand what i'm on about:

Code: [Select]
$sql = "INSERT INTO teams (company, teamname, teamnum, amountraised, country, president) VALUES ";

// $rows is a count of the rows in the csv

for($i=1; $i<$rows; $i++){

$sql.="('{$myarray[$i][0]}','{$myarray[$i][1]}','{$myarray[$i][2]}','{$myarray[$i][3]}','{$myarray[$i][4]}','{$myarray[$i][5]}')";
echo $i . "<br/>";

if($i >= 1 && $i < $rows - 1)
{
$sql.= ",";
}
}

Anyway, the issue is that one of the fields("teamnum") needs to be unique - so i've set this as unique on the table in mysql.

But when i run my query it doesn't import anything if one of the records isn't unique. What i really want is for it to import the ones it can and catch the ones it cant import to present to the user.

So my question is - to acheive the above would i need to rewrite the query so that it inserts each row one at a time, instead of all together?

Or can someone point me in the right direction for a better solution?

Probably something very simple i've missed i am sure...

Thanks chaps!

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=313325.0

This topic has been moved to Third Party PHP Scripts.

http://www.phpfreaks.com/forums/index.php?topic=325508.0

I am trying to array data to database against same id. Here is code. form.php

<form name="users" method="post" action="order_submit.php" enctype="multipart/form-data" onSubmit="return validate();" id="inv_form">
<div class="formSep">
<select name="company" onChange="showSubcat(this);">
<option value="">Company</option>
<?php $s1 = mysql_query("select * from leads where lead_customer='Lead' ") or die (mysql_error());
while($s2 = mysql_fetch_array($s1))
{ ?>
<option value="<?php echo $s2['id']; ?>"><?php echo $s2['company']; ?></option>


<?php 


} ?>
</select>

</div>

<div class="formSep">

<table class="table invE_table">
<thead>
<tr>
<th></th>
<th>Item</th>
<th>Unit</th>
<th>Unit Cost ($)</th>
<th>Qty</th>
<th>Tax (%)</th>
<th>Total ($)</th>
</tr>
</thead>
<tbody>
<tr class="inv_row">
<td class="inv_clone_row"><i class="icon-plus inv_clone_btn"></i></td>
<td><input type="text" class="span12" name="invE_item[]" /></td>
<td><input type="text" class="span12" name="invE_description[]" /></td>
<td><input type="text" class="span12 jQinv_item_unit" name="invE_unit_cost[]" /></td>
<td><input type="text" class="span12 jQinv_item_qty" name="invE_qty[]" /></td>
<td><input type="text" class="span12 jQinv_item_tax" name="invE_tax[]" /></td>
<td><input type="text" readonly class="span12 jQinv_item_total" name="invE_total[]" /></td>
</tr>
<tr class="last_row">
<td colspan="5">&nbsp;</td>
<td colspan="2">
<p class="clearfix">Subtotal: <span class="invE_subtotal">$<span>0.00</span></span></p>
<p>Tax: <span class="invE_tax">$<span>0.00</span></span></p>
<p>Discount: <span class="invE_discount">$<span>0.00</span></span></p>
<p><strong>Balance: <span class="invE_balance">$<span>0.00</span></span></strong></p>
</td>
</tr>
</tbody> 
</table>
</div>

Here invE_item[], invE_description[], invE_unit_cost[].... are the array , i mean dynamically one can add as many as items and its details. In my order_submit.php page

<?php

error_reporting(0);
include("connect.php");
include("admin_auth.php"); 

if(isset($_POST['save']))
{

$company = $_POST['company'];
$contact_person = $_POST['contact_person'];
$billing = $_POST['billing_address'];
$shipping = $_POST['shipping_address'];
$reference = $_POST['reference'];
$t_c = $_POST['t_c'];
$payment = $_POST['payment'];
$ship_in = $_POST['ship_inst'];
$validity = $_POST['validity'];
$currency = $_POST['currency'];
$order_for = $_POST['order_for'];
$assigned_to = $_POST['assigned_to'];
$item = $_POST['invE_item'];
$unit = $_POST['invE_description'];
$price = $_POST['invE_unit_cost'];  
$qty= $_POST['invE_qty'];   
$tax = $_POST['invE_tax'];  
$total = $_POST['invE_total'];  

$sql = mysql_query("insert into orders (order_id, company_id, contact_person, billing_address, shipping_address, reference, t_c, payment, shipping_inst, validity, order_for, currency, assigned_to, last_modified, order_quote) values ('', ".$company.", '".$contact_person."', '".$billing."', '".$shipping."', '".$reference."', '".$t_c."', '".$payment."', '".$ship_in."', ".$validity.", '".$order_for."', '".$currency."', '".$assigned_to."', NOW(), 'Order')");

$last_id = mysql_insert_id();

$msql = "insert into order_line_items (id, order_id, company_id, item, unit, unit_cost, quantity, tax, total) values ('', ".$last_id.", ".$company.", '".$item."', '".$unit."', ".$price.", ".$qty.", ".$tax.", ".$total.")";


$l1 = mysql_query($msql) or die (mysql_error());    
} 

I want to insert each item in different row with $last_id , as in the attached image .   Please somebody help me in this Attached Files  db.PNG   11.01KB   4 downloads