PHP - Moved: Changing The Case (upper To Capitalized) Of 20,000+ Rows Of Data At Once?

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

what php function would I need to use to let's say I have a string

I went to the Store

And I want to pass that string, or any string through some type of function that would output:

I Went To The Store

How in the world could I do this?  am I overthinking this or is this extremely difficult?

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

hello dear php-experts,

i fairly new to simple_html_dom usage and methods. I know a little the parser,
 
i want to gather some information from this site:

https://europa.eu/youth/volunteering/organisations_en#open

is this possible to get the content - of let us say 10 or 20 last records on that page - and subesquently to store it in my mysql - db!?

<?php
// Report all PHP errors (see changelog)
error_reporting(E_ALL);

include('inc/simple_html_dom.php');

    //base url
    $base = 'https://europa.eu/youth/volunteering/organisations_en#open';

    //home page HTML
    $html_base = file_get_html( $base );

    //get all category links
    foreach($html_base->find('a') as $element) {
        echo "<pre>";
        print_r( $element->href );
        echo "</pre>";
    }

    $html_base->clear(); 
    unset($html_base);

?>

 

I have the above code and I'm trying to get certain elements of the page but it isn't returning anything. 

 

Is it possible that certain PHP functions might be disabled on the server to stop that?

The above code works perfectly on other sites.

 

Is there any workaround?

 

 

btw:  i have created a small snipped as a proof of concept to run this with Python and BeautifulSoup - 

 

import requests
from bs4 import BeautifulSoup
 
url = 'https://europa.eu/youth/volunteering/organisations_en#open'
response = requests.get(url)
soup = BeautifulSoup(response.content, 'lxml')
print(soup.find('title').text)
block = soup.find('div', class_="eyp-card block-is-flex")

 

and this.... 

 

European Youth Portal
>>> block.a
<a href="/youth/volunteering/organisation/48592_en" target="_blank">"Academy for Peace and Development" Union</a>
>>> block.a.text
'"Academy for Peace and Development" Union'
 
>>> block.select_one('div > div > p:nth-child(9)')
<p><strong>PIC:</strong> 948417016</p>
>>> block.select_one('div > div > p:nth-child(9)').text
'PIC: 948417016'

 

what is aimed in the end - i want to gather the first 20 results of the page - and put them in to a sql-db or alternatively show the information in a little widget 

Hello,   This is confusing me! - this code is finding 2 rows of data ($nrows is 2), but I'm only getting 1 row of data returned. On the second while loop I'm getting the following error message:   Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/website.com/test.php on line 26

				$query ="SELECT * FROM Accounts WHERE (jaaLatitude = '' OR jaaLongitude = '')";	
				$result=mysql_query($query)  
					or die ("Connection to database table failed."  . mysql_error());
				$nrows=mysql_num_rows($result);

				$i = 0;
				if ($nrows > 0)
				{	
					while($record = mysql_fetch_array($result, MYSQL_BOTH))
						{	
							$jaaIndex = $record[jaaIndex];	
							$jaaEmail = $record[jaaEmail];
							$jaaLocation = $record[jaaLocation];
							$jaaLongitude = $record[jaaLongitude];
							$jaaLatitude = $record[jaaLatitude];

                                                        echo "<br>test jaaEmail is $jaaEmail<br>";

                                                        $i = $i + 1; 
                                                 }
                                 }

As I say the first while loop is working, so I don't see what can be wrong with the qurery (it's not failing).   Thanks for your help,   S

Hey guys,

I got another one that i could use some help on.  I have a input form that utilizes a javascript that adds additional rows to my table.  here is a look at what i got.

Code: [Select]
<script type="text/javascript">
function insertRow()
{
    var x = document.getElementById('results_table').insertRow(-1);
    var a = x.insertCell(0);
    var b = x.insertCell(1);
    var c = x.insertCell(2);
    var d = x.insertCell(3);
    var e = x.insertCell(4);

    a.innerHTML="<input type=\"text\" name=\"id\">";
    b.innerHTML="<input type=\"text\" name=\"place\">";
    c.innerHTML="<input type=\"text\" name=\"username\">";
    d.innerHTML="<input type=\"text\" name=\"earnings\">";
    e.innerHTML="<input type=\"button\" value=\"delete\" onClick=\"deleteRow(this)\">";
}

function deleteRow(r)
{
    var i=r.parentNode.parentNode.rowIndex;
    document.getElementById('results_table').deleteRow(i);
}

</script>
this is my code stored in the header of my page.  basically just a button to add a new row and a button in each row to delete rows if needed.

here is a look at my html table, pretty basic...

Code: [Select]
<table id="results_table">

            <th>Event ID</th><th>Place</th><th>Username</th><th>Earnings</th>

            <tr>
                <td><input type="text" name="id"></td><td><input type="text" name="place"></td><td><input type="text" name="username"></td><td><input type="text" name="earnings"></td><td><input type="button" value="delete" onClick="deleteRow(this)"</td>
            </tr>

        </table>
Now what I am hoping to acheive is once i submit all of these rows to the database i will insert each of these rows into their own row in the database.  is this doable?

the structure of my database is:

ResultID (Primary Key)
Place
Earnings
UserID (Foreign Key)
EventID (Foreign Key)


now i think the biggest problem i'm having with submitting data to the database is that in the original HTML form I am typing in the actual username and not the userID. 

so when it comes to processing I need to do a switch of these two things before I run my query.  Should something like this work?

Code: [Select]

$username = $_POST['username'];

$userID = "SELECT userID FROM users WHERE username="$username";

$query = mysql_query($userID);



also i've never tried to submit multiple entries at a time.  maybe i have to create an array somehow in order to capture each rows data.  any thoughts?

as always thanks for the help.

Hi,
I am trying to output my data call rows, alongside each other till two, then break and start a new row. and so on and so on.
this is not really a example question, i think its more a request information question and example question.

How would i go about $outputting the rows alongside each other?

i have tried:

$output_rows['MYDATA']='then in here i have had a complete table with all the css and styling and information entered'; //but i couldn't get it alongside each other on the html page?

then i tried outputting the information and had the tables and css setup on the .html page, but that gave me duplicate entries alongside each other, as no i was entering data from the {MYDATA}.. Twice..

I am really stuck!
if any one can assist Great.   

So when I execute this it'll go for about 130k rows then give a 500 error. Any ideas how to avoid this and get it to complete?

Code: [Select]
$i=10000000;
while($i<=99999999)
  {
  $public = $i;
  $value = rand(10000000, 99999999); 
  mysql_query("INSERT INTO yummy_table (public, value) VALUES('$public', '$value' ) ") or die(mysql_error()); 
  $i++;
  }
echo "done";

Here is a link to the website I am designing. http://173.167.65.189/singleton/
the username and password is ksingleton
at the bottom where the calendar is the query is searching for scheduled events, i am only posting the company_id right now,  the problem I am having is I want a scheduled event that continues through the week to stay in line with the one before.  But if the schedule before ended and a new one continues it moves the next date company_id up.  How can i align the table rows or create an empty table row on all other days if there was a schedule before.  here is the code for the calendar.

Quote

<?php
$db = new mysqli();
$setdate=strtotime($_GET['date']);
$session=$_GET['session'];
$calview=$_GET['calview'];
if ($setdate <> "") {
   $date=$setdate;
} else {
$date=time();
}
$day=date('d',$date);
$month=date('m',$date);
$year=date('Y',$date);
$first_day=mktime(0,0,0,$month,1,$year);
$title=date('F',$first_day);

$day_of_week = date('D', $first_day);

switch($day_of_week) {
case "Sun": $blank = 0;break;
case "Mon": $blank = 1;break;
case "Tue": $blank = 2;break;
case "Wed": $blank = 3;break;
case "Thu": $blank = 4;break;
case "Fri": $blank = 5;break;
case "Sat": $blank = 6;break;
}

$days_in_month = cal_days_in_month(0, $month, $year);

echo "<table cellpadding=0 cellspacing=0 border=0>";
echo "<tbody>";
echo "<tr>";
echo "<td><a href=?session=$session&calview=day>Day</a></td>";
echo "<td><a href=?session=$session&calview=week>Week</a></td>";
echo "<td><a href=?session=$session&calview=month>Month</a></td>";
echo "</tr>";
echo   "<tr>";
echo      "<td colspan=3>";
if ($calview=="month") {
echo         "<table border=1 width=800>";
echo            "<tbody>";
echo               "<tr height=25><th colspan=7> $title $year </th></tr>";
echo               "<tr height=25><td width=58 align=center>Sunday</td><td width=58 align=center>Monday</td><td width=58 align=center>Tuesday</td><td width=58 align=center>Wednesday</td><td width=58 align=center>Thursday</td><td width=58 align=center>Friday</td><td width=58 align=center>Saturday</td></tr>";

$day_count = 1;

echo               "<tr height=50>";

while ( $blank > 0 ) {
echo               "<td></td>";
$blank = $blank-1;
$day_count++;
}

$day_num = 1;

while ($day_num <= $days_in_month ) {
$newdate = $month ."/". $day_num ."/". $year;
$msqlnewdate = date('m/d/Y',strtotime($newdate));
echo "<td valign=top><table cellpadding=0 cellspacing=0 border=0><tr><td><a href=?session=$session&calview=day&date=$newdate> $day_num </a></td></tr>";
$db = new mysqli('localhost','username','password', 'rsdata');

if (!$db) {
   echo 'ERROR: Could not connect to database.';
} else {
$query = $db->query("SELECT * FROM rentals WHERE date_format(str_to_date(pdate_out,'%Y-%m-%d'), '%m/%d/%Y') <= '$msqlnewdate' AND date_format(str_to_date(pdate_in, '%Y-%m-%d'), '%m/%d/%Y') >= '$msqlnewdate'");
if ($query) {
   while ($result = $query->fetch_object()) {
      echo "<tr><td>".$result->company_id."</td></tr>";
   }
}
echo "</table>";
}


$day_num++;
$day_count++;

if ($day_count > 7) {
echo "</tr><tr>";
$day_count=1;
}
}

while ($day_count >1 && $day_count <=7 ) {
echo "<td></td>";
$day_count++;
}
echo "</tr></table>";
} else if ($calview=="week") {

} else {

}
         
echo      "</td>";
echo   "</tr>";
echo "</tbody>";
echo "</table>";
?>


MOD EDIT: DB credentials edited out.

i'm trying to echo out the results from a query onto a page, but rather than having them echo out as rows in a table, i want them to be displayed in columns, i.e. first column would have the first 10 results and the second column would have the next to. Any ideas on how i could do this?

at the moment i'm using this code to display my results

$read=mysql_query("SELECT * FROM entry WHERE category_id=".$id." ORDER BY entry_title") or die("query failed".mysql_error());
$result=mysql_num_rows($read);
for($j = 0; $j < $result; $j++) { 
   

$row = mysql_fetch_array($read);

echo "<a href=article.php?id=".$row['entry_id']. ">".$row['entry_title']."</a><br />";
}

but this just displays it as a list down the middle

thanks

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.

Hi,

I'm having a bit of trouble with returning data from the database. It brings back the images etc... correctly, but I need to be able to limit it to two images per, line and then to carriage return. Or using CSS to position them with the correct padding etc... I'm very tired and can't seem to figure it out, which is starting to frustrate me.

To see what I mean, go to www.bikescarsandvans.co.uk/test.php and select the drop down menu.

And see coding for particular section.

Any help will be much appericated, I know a good sleep would probaly help but don't have time for that right now 

Code: [Select]
 
  print "
</td>
<td colspan='2'>
<div id='CollapsiblePanel" . $car_row["id"] . "' class='CollapsiblePanel'>
  <div class='CollapsiblePanelTab' tabindex='0'><img class='drop' src='images/drop_down.jpg' alt='Go' /></div>
  <div class='CollapsiblePanelContent'>
  <p>
<div class='title_tabs'>Standard Specification</div>
<table class='standard_spec'>
<tr>
<th>
Engine Size:
</th>
<td>
".$car_row["engine"]."
</td>
</tr>
<tr>
<th>
BHP:
</th>
<td>
".$car_row["bhp"]."
</td>
</tr>
<tr>
<th>
Fuel Type:
</th>
<td>
".$car_row["fuel_type"]."
</td>
</tr>
<tr>
<th>
Number of Doors:
</th>
<td>
".$car_row["no_doors"]."
</td>
</tr>
<tr>
<th>
Wheels:
</th>
<td>
".$car_row["wheels"]."
</td>
</tr>
</table>     
</p>
";

//QUERY FOR TITLE START
$query_title = "SELECT * FROM extras JOIN car_to_extra ON (car_to_extra.extras_id = extras.id) WHERE car_to_extra.car_id = '{$car_row['id']}' ORDER BY extras.price ASC";
$title_results = mysql_query($query_title) or die ("Error in query: $query_title. ".mysql_error());
$current_heading = '';
while ($title_row = @ mysql_fetch_array($title_results ))
{
if ($current_heading != $title_row["title"])
{
    // The heading has changed from before, so print the new heading here.
    $current_heading = $title_row["title"];
    print "


<p>
<div class='title_tabs'>" . $title_row["title"] . "</div>
  ";
  }

?>
                                           
                                            <a class='data' href='#' onmouseout='hideTooltip()' onmouseover='showTooltip(event,"<?php print "" . $title_row["info"] . "<br/>(&pound;" . $title_row["price"] . ")"; ?>");return false'>
                                      <?php
print "
<img class='extra_img' src=\"". $title_row["img"] ."\" alt='" . $title_row["img_alt"] . "' /></a>
</p>";  


}
//QUERY FOR TITLE END

 
print "  
  </div>
</div>
<script type='text/javascript'>
<!--
var CollapsiblePanel" . $car_row["id"] . " = new Spry.Widget.CollapsiblePanel('CollapsiblePanel" . $car_row["id"] . "', {contentIsOpen:false});
//-->
</script>
</td>
</tr>
";

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!

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