PHP - Moved: Retrieving Multiple Rows With Duplicate Data

This topic has been moved to MySQL Help.

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

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!

Ok so I am pretty good with mysql and performing queries to get the data that I need. However, I have a question for any of you gurus out there that may be able to help me with an issue that I always run into with certain types of queries. Say I have 2 tables like this:

Galleries
id name 1 My Photos
Gallery Photos
id gallery_id photo 1 1 photo1.jpg 2 1 photo2.jpg 3 1 photo3.jpg
Now usually when I pull this info from the database I have to do 2 separate queries in order to get the data and then link them so I would do something like.


<?php
//make the gallery query
$query = "SELECT * FROM galleries";
$results = mysql_query($query);

//setup an array for the galleries data
$gallery_data = array();

//loop through the galleries
for($i=0;$i<mysql_num_rows($results);$i++){
//add the gallery info to the gallery data array
$gallery_data[$i] = mysql_fetch_array($results);

//make the photos query with the gallery id
$photo_query = "SELECT * FROM gallery_photos WHERE gallery_id='".mysql_real_escape_string($gallery_data[$i]['id'])."'";
$photo_results = mysql_query($photo_query);

//setup the photos array
$photo_data = array();

//put the photos in the array
for($n=0;$n<mysql_num_rows($photo_results);$n++){
$photo_data[$n] = mysql_fetch_array($photo_results);
}

//add the photos to the gallery array
$gallery_data[$i]['photos'] = $photo_data;
}

//now to display it is like this
if(is_array($gallery_data)){
foreach($gallery_data as $gallery){
echo $gallery['name'];

//show the photos
if(is_array($gallery['photos'])){
foreach($gallery['photos'] as $photo){
echo $photo['photo'];
}
}
}
}
?>


So my question is there a way to get all of this data at one time. I know how to do multiple queries in one and to do joins but they can only return one row as far as I know of. The only other way that I know how to do this is by ordering them by name and selecting them directly from the photos table and then just getting the gallery name like this:


SELECT *,(SELECT name FROM galleries WHERE id=gallery_photos.id) AS gallery_name FROM gallery_photos ORDER BY gallery_id


Then I could just do one loop and see if the name has changed and if so to output the new name. But I would like to know if there is a way to get a second set of results as an array from a query so I could just select the galleries and photos all in one query. Any help is appreciated.

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

This topic has been moved to Third Party PHP Scripts.

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

This topic has been moved to MySQL Help.

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

hello, im only getting the second half of my results showing in my dropdown box.  here is my code.

Code: [Select]
if(isset($_GET['id']))
{
$uid = $_GET['id'];
}

$forward = mysql_query("SELECT * FROM players WHERE status = '1' AND position = 'forward' ORDER BY name");
$forward1="";
while($row1 = mysql_fetch_array($forward))
  {
$forwardid=$row1["id"];
$forwardname=$row1["name"];
$forwardteam=$row1["team"];
$forward1.="<OPTION TITLE=\"$forwardteam\" VALUE=\"$forwardid\">".$forwardname.'</option>';
  }

$result = mysql_query("SELECT * FROM picks WHERE uid = '$uid' AND pickid = 'forward1'");
while($row = mysql_fetch_array($result))
{
  $playerid=$row['playerid'];
}

if(isset($forwardpick1)) {
$result1 = mysql_query("SELECT * FROM players WHERE id = '$playerid'");
while($row1 = mysql_fetch_array($result1))
{
  $forwardpick1=$row1['name'];   
}
$forwardpick1="<br /><br /><font color ='#000000' size = '5'><b>$forwardpick1";
} else {
$forwardpick1="
<form method='POST' action='draftpick.php' name='forward1'>
<p><font color='#000000'><select size='1' name='player'>
<?php echo '$forward1'; ?>
</select><br><br><input type='submit' value='Submit' name='submit'></font></p>
</form>
";
}
any direction is appreciated.

My site seems to be going well, but on the back end, the users database is going crazy with duplicate entrys. but its not doing it from an INSERT. it seems to be doing it with this code:
Code: [Select]
               $bitquery = $bitcoin->move("bittleship$username", "bittleships", $cost, $minconfirmations);
               if ($bitquery == true){
               $query = mysql_query("INSERT INTO `transactions` ( `id` , `username` , `type` , `amount`, `address`) VALUES ('', '$username', 'buyclicks', '$cost', '')")  or die(mysql_error());
               $query = mysql_query("UPDATE users SET `clicks` = `clicks`+$quantity, `balance`=`balance`-$cost WHERE `username` = '$username' ") or die(mysql_error());
               include('update.php');

Alright,
I need to find a way to display the top ten duplicated rows for a table in my database. I have tried multiple methods but I have failed. Can you please assist me with this problem. I currently run a query in phpmyadmin to get the result, but i cant figure out how to properly code the php script.

This topic has been moved to MySQL Help.

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

I'm pulling data from an xml file and I'm getting a lot of duplicates. How can I remove the duplicates from the query?

Code: [Select]
foreach($xml->category->subcategory as $category){
echo $category[id];
}//end foreach

Ok I'm trying to insert multiple rows by using a while loop but having problems. At the same time, need to open a new mysql connection while running the insert query, close it then open the previous mysql connection.

I managed to insert multiple queries before using a loop, but for this time, the loop does not work? 
I think it is because I am opening another connection... yh that would make sense actually?

Here is the code:


$users = safe_query("SELECT * FROM ".PREFIX."user");
       while($dp=mysql_fetch_array($users)) { 
          
   $username = $dp['username'];
 $nickname = $dp['nickname'];
 $pwd1 = $dp['password']; 
 $mail = $dp['email'];
 $ip_add = $dp['ip'];
 $wsID = $dp['userID'];
 $registerdate = $dp['registerdate'];
 $birthday = $dp['birthday'];
 $avatar = $dp['avatar'];
 $icq = $dp['icq']; 
 $hp = $dp['homepage'];
 
echo $username." = 1 username only? :(";
      

// ----- Forum Bridge user insert -----

$result = safe_query("SELECT * FROM `".PREFIX."forum`");
$ds=mysql_fetch_array($result);
$forum_prefix = $ds['prefix'];
define(PREFIX_FORUM, $forum_prefix);
define(FORUMREG_DEBUG, 0);

$con = mysql_connect($ds['host'], $ds['user'], $ds['password']) or system_error('ERROR: Can not connect to MySQL-Server');
$condb = mysql_select_db($ds['db'], $con) or system_error('ERROR: Can not connect to database "'.$ds['db'].'"');
                
include('../_phpbb_func.php');
$phpbbpass = phpbb_hash($pwd1);
$phpbbmailhash = phpbb_email_hash($mail);
$phpbbsalt = unique_id();

safe_query("INSERT INTO `".PREFIX_FORUM."users` (`username`, `username_clean`, `user_password`, `user_pass_convert`, `user_email`, `user_email_hash`, `group_id`, `user_type`, `user_regdate`, `user_passchg`, `user_lastvisit`, `user_lastmark`, `user_new`, `user_options`, `user_form_salt`, `user_ip`, `wsID`, `user_birthday`, `user_avatar`, `user_icq`, `user_website`) VALUES ('$username', '$username', '$phpbbpass', '0', '$mail', '$phpbbmailhash', '2', '0', '$registerdate', '$registerdate', '$registerdate', '$registerdate', '1', '230271', '$phpbbsalt', '$ip_add', '$wsID', '$birthday', '$avatar', '$icq', '$hp')");
if (FORUMREG_DEBUG == '1') {
echo "<p><b>-- DEBUG -- : User added: ".mysql_affected_rows($con)."<br />";
       echo "<br />-- DEBUG -- : Query used: ".end($_mysql_querys)."</b></p><br />";
      
       $result = safe_query("SELECT user_id from ".PREFIX_FORUM."users WHERE username = '$username'");
       $phpbbid = mysql_fetch_row($result);
safe_query("INSERT INTO `".PREFIX_FORUM."user_group` (`group_id`, `user_id`, `group_leader`, `user_pending`) VALUES ('2', '$phpbbid[0]', '0', '0')");
safe_query("INSERT INTO `".PREFIX_FORUM."user_group` (`group_id`, `user_id`, `group_leader`, `user_pending`) VALUES ('7', '$phpbbid[0]', '0', '0')");
mysql_close($con);
}

         include('../_mysql.php');        
         mysql_connect($host, $user, $pwd) or system_error('ERROR: Can not connect to MySQL-Server');
mysql_select_db($db) or system_error('ERROR: Can not connect to database "'.$db.'"');
       
       }



So I need to be able to insert these rows using the while loop.. how can I do this?

I really appreciate any help.

I have a basic table where I am trying to retrieve records that are filtered by a form request. Here is the code I have...

$ps = $pdo->prepare("SELECT * FROM `Products` Where `Vendor` LIKE concat('%',?,'%');
$ps->execute(array($_POST['Vendor']));;
echo "post=" . $_POST['vendor']; ////Displays correct data from form request
$count = $ps->rowCount();
echo "Count=" . $count; ////Count = 0 although I know for a fact that there is 1 record that should be in there

////Used for display of records
foreach ($ps as $row){
echo $row, PHP_EOL . "xxx<br>";
}

Where is the incorrect code? I am new to php and pdo. Thank you in advance

K

Hi,
I need to get data from an XML file, like the format below:
Code: [Select]
<ISBNdb server_time="2011-05-31T19:19:42Z">
<BookList total_results="1" page_size="10" page_number="1" shown_results="1">
<BookData book_id="build_your_own_database_driven_web_site_using_php_mysql_4th_" isbn="0980576814" isbn13="9780980576818">
<Title>
Build Your Own Database Driven Web Site Using PHP & MySQL, 4th Edition
</Title>
<TitleLong/>
<AuthorsText>Kevin Yank, </AuthorsText>
<PublisherText publisher_id="sitepoint">SitePoint</PublisherText>
<Details change_time="2009-04-02T00:30:10Z" price_time="2011-05-31T17:46:10Z" edition_info="Paperback; 2009-06-15" language="" physical_description_text="360 pages" lcc_number="" dewey_decimal_normalized="5" dewey_decimal="005"/>
</BookData>
</BookList>
</ISBNdb>
I need to know how you get the details from <title>,<AuthorsText><PublisherText> and Bookdata. Tried using the example on W3C, using simplexml_load_file but it only returns:
Code: [Select]
ISBNdb
BookList:
Code I'm currently using is:
Code: [Select]
$xml = simplexml_load_file("http://isbndb.com/api/books.xml?access_key=----&results=details&index1=isbn&value1=".$isbn);
echo $xml->getName() . "<br />";

foreach($xml->children() as $child)
  {
  echo $child->getName() . ": " . $child . "<br />";
  }

I would like to display weather conditions on my website and store them in mysql database.
I wonder if it is possible to load web page into php for parsing so the required info could be found and used?
Is it doable with php and what functions could you recommend for this functionality?
How can I have program running in the background on the server which would be triggered every so often to perform this task?
Another question is if I want to trigger some action once a week like Tuesday at noon, is there function in php which could be used to check what day of the week and time it is ?
I am new to website design so any help will be greatly appreciated.