Posts by mpc123

first time ever I haven't had a reply, anybody?

Sorry the bottom part should read

For example I would need to update last_time on id 1 with id 4 score / holes, then id4 would be need last_time updating with id 7 score / holes and so on and so on. This table has about 100000 rows in and each date could have differening number of people taking part of course all with different names, with this I need to get the info for each person of course if they occur more than once.

I hope someone can help wit this, Im reallly struggling.

Can anyone help?

I have a table of names that have done something on a certain date, now this could be over and over again yet of course on different dates but never the same. I need to update a field called last_time that corresponds to the previous date that they occur and update the last_time with a sum of 2 other fields,

Quick example Table

id , date , name , score, holes, last_time

1, 2015-01-01, him 1, 80, 18, (NULL)
2, 2015-01-01, him 2, 79, 18, (NULL)
3, 2015-01-01, him 3, 45, 18, (NULL)
4, 2015-01-02, him 1, 79, 9, (NULL)
5, 2015-01-02, him 2, 50, 9, (NULL)
6, 2015-01-02, him 3, 20, 9, (NULL)
7, 2015-01-03, him 1, 50, 18, (NULL)
8, 2015-01-03, him 2, 40, 18, (NULL)
9, 2015-01-03, him 3, 90, 18, (NULL)

Of course singularly and on the same id would be

update exampleTable set last_time = (score / holes) where name ="him 1"

But I need from the previous dates info.

For example I would need to update last_time on id 1 with id 4 score / holes, then id would be need last_time updating with id 7 score / holes. This table has about 100000 rows in and each date could have differening number of people taking part of course all with different names, with this I need to get the info for each person of course if they occur more than once.

I hope someone can help wit this, Im reallly struggling.

BTW that works great thank you very much !!

WOW thanks for the advice, I will change i and also try what you have sent me, thanks again

Anybody give me any idea at least?

I have tried in xmlhttp.open setting &$mode= but I can't get it to work

Can anybody tell me how to define $mode please?

Great thanks that worked perfect.

Thanks Again

Hi

I need to look in a field to find the text same as another field I have this at the moment but it does not work, hoep you can help

update TAB1, TAB2 set `field3` =`field4` where FIND_IN_SET('`FIELD1`', '`FIELD2`')

I feel FIND_IN_SET is not the right thing as the 2 fields may be

field1 - blue car
field2 - blue car bits

Thanks

thanks for your replies, how would I define $mode also please?

Hi

I have a varible that is set at as a different value for each page this is called $mode.

Currently I have succesfully set up a drop down menu that can filter the table and refresh the results based on that filter chosen, yet I need to call this $mode variable into the query to be able to get the true results back that I require.

The php page to call the filter code is as follows

<?php
$q = intval($_GET['q']);

$con = mysqli_connect('x','x','x');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"x");
$sql="SELECT * FROM tab1 WHERE info = '".$q."' and id='$mode' LIMIT 10";
$result = mysqli_query($con,$sql);

echo "blar blar blar

and another one that shows the results on the page

<script>
function showUser(str) {
    if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
    } else { 
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("txtHint").innerHTML = xmlhttp.responseText;
            }
        }
        xmlhttp.open("GET","info.php?q="+str,true);
        xmlhttp.send();
    }
}
</script>


<form>
<select name="Info" onchange="showUser(this.value)">
  <option value="">Select a mod:</option>
  <option value="1">this</option>
  <option value="2">that</option>
  <option value="3">other</option>
  </select>
</form>
<br>
<div id="txtHint"><b>NEw info shows here</b></div>

IN the page that these pages that I echo the above page into, it has the variable $mode
, which sucessfully is iised in other queries in the page but $q is passed …

Hi

I need to delete words before a set of words in a field.

eg delete the words before the word price in that field.

I already have a query to replace a certain word but cant see how adapt it to do what I require
update TABLE set field = replace(field,'price','');

can somebody help please?
Thanks in advance

anything else somebody can offer please?

Hi that looks great but the info in the database does change alot

So I have a single drop down menu populated from data base, now i want to get a bit snazzy and select from the first and from the selected then populate the 2nd drop down to select and then link to a url.

Here is my code for first drop down which is populating fine, where do i go from here?

$query = "SELECT DISTINCT Manufacturer from manufacturers  ORDER BY Manufacturer ASC";
// Execute it, or return the error message if there's a problem.
$result = mysql_query($query) or die(mysql_error());

$dropdown = "<select name='products'>";

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

  $dropdown .= "\r\n<option value='{$row['Manufacturer']}'>{$row['Manufacturer']}</option>";

}

$dropdown .= "\r\n</select>";

echo $dropdown;

mysql_close($con);
?>

Hi thanks for your help i had set my result as $row instead of $file therefore there was no result

I have this to make a file ... but the file created is only ever .php, it is like it is not echoing the field name.

$newfile ="" . str_replace(' ','-',trim($file['title'])) . ".php";

and the next one i tried to echo out in the page came out blank too?

<?php $txt1 = "'. $file['title'] . '"?>

Hope some one can help :)

Hi

I have changed some urls and know how to do a normal redirect but do not know where to start to be abale to redirect if part of the url contains certain words Example

thisdomain.com/this/now-when/ would be redireted to thisdomain.com/this/an-other/

There is quite a few of them with the ending so cant just type each one in as a normal redirect.

Hope someone can help

Thanks

sorry i have now managed to make it work by just taking out the word SUM

Hi

I need to update a colomn with the result of colomn 1 divided colomn2

I have tried

update TABLE set colomn =SUM(colomn1 / colomn2) 

but get [Err] 1111 - Invalid use of group function

IF anyone can help please

Great thanks for the quick reply, stange i already use indexes on those tables but looking at the indexes set they do not include those 2 colomns I am working on.

Thanks again for the heads up

HI

Hope somebody can help. My query updates a colomn in tab1 from a row in tab2 but only if a colomn in tab1 is equal to that of a row in tab2.

tab 1 has 360000 rows
tab 2 has 477 rows

The update query runs for ages and still does not complete or when I execute it seems nothing has been updated.
Hopefully we can make this quicker. Thanks

I currently have this update query

update tab1, tab2 set _tab1.info = tab2.info where tab1.info2 = tab2.info2;

Of course what a plonker - thanks for your help

HI I have an else if statement that all the results are coming out as the first if even though in the info row they are different in the table, hope someone can help.

// showbanners
                            if($row['info'] ="Some Apples")
                            {
                                $showbanners = '<img src="1.png"><br><br><img 

src="2.png">';
                            } 
                            elseif($row['info'] ="Some Bananas")
                            {
                                $showbanners = '<img src="1.png"></span>';
                            }
                            else
                            {
                                $showbanners = '<img src="1.png"></span>';
                            }

ok thanks

WOW too complicated for me , any advice for a start?

Hi thanks for reply, yes but i cant find a query in mysql that will update the field to ucwords

do you have that option please? Itried it with this but got FUNCTION ucwords does not exist

UPDATE `TABLE` SET `field` = ucwords(`field`);

Hi Im haveing a problem with ucwords and FINDINSET in a query I have tried all sortsof ways but just get errors

I currently have and get FUNCTION ucwords does not exist. Can anybody help please

$query = "SELECT * FROM TABLE1, TABLE2 WHERE FIND_IN_SET(ucwords(TABLE1.field), TABLE2.field)"; 

perfect squidge and now sorted.

Thanks cmps for replying

i need the replace also though?

I have now got it to work to echo out the fields
BUT the ucwords is not working with the code that Squidge provided

thanks for the reply but still both dont echo out

I have the following code but when $newstring echos out as
'.-ucwords(strtolower(-['First.Name']))-.-'-'.-ucwords(strtolower(-['Last.Name']))-.-.php

It should echo out the fields from the database noy just that code.

If someone could help please

$txtname = "'. ucwords(strtolower($row ['First.Name'])) . '-'. ucwords(strtolower($row ['Last.Name'])) . "; 
$newstring = str_replace(" ", "-", $txtname);
while($row = mysql_fetch_array($result))

  {

                           echo'



                                <div class="cat-post-desc">
                                    <h3><a href="' . $newstring . '.php">' . $row['First.Name'] . ' ' . $row['Last.Name'] . '</a></h3>

                                    <p></p>
                                </div>';

its ok i replaced the GROUP_CONCAT(id) with GROUP_CONCAT(Table.id) and this now works, now to try and do the update?

i have got a result to come out after running that query but jusy with one row for now out of the thousands of rows

EG it groups the first one in the field and not the rest seperated by , . So where by it should select multiple id's it only selects the first one it finds in the set. How will i make it select all ids for all of the sets

so line 2 would be

FROM TABLE 2 NAME TABLE 2 NAME ?

OK thanks i will try can you tell me what the replace2 represents please, sorry for being thick

firstly thankls for the reply but sorry im not that advanced, so i dont know how I would update from that

SORRY

the TABLE 2 fields to use would be ID and product_name

they are not joined
the TABLE 1 fields to use would be ID and product_name

Hi I have an issue where by I have lots of products in a field which are comma delimited I want to replace those product words with a id number that I have in a different table.
I currently use FIND INSET to replace a tab to the comma but currently cant see how I would you that type to change what I need to.

Here is my data

EG
Table 1
product_field
car,boat,shirt,tie

Table 2

id product_name
1 car
2 boat
3 shirt
4 tie

So I need to replace Table 1 product_field with Table 2.id so result would be

Table 1

product_field
1,2,3,4

Can anyone help please

data eg

Field Field2 naughtyfield
blar 7 1
blar 5 2
blar 4 3
blar 9 4
blar 6 5
blar 5 6
blar 1 1
blar 2 2
blar 5 3
blar 4 4
blar 6 5
blar 12 6

result

1-1
2-1
3-4
4-4
5-6
6-5

`

$result = $dba->query()->fetchAll(PDO::FETCH_ASSOC);

$dba = null;

$break = 3;

$lineend = $break;


        echo '' . $row['field'] . ' ' . $row['naughtyfield'] . ' ' . $row['field'] . '';



        echo '</ul>
                        </div>
                </div>
                <div class="threeColFooter">
                </div>';

        echo '</div>';

        if(($k + 1) == $break)
        {
                echo '<div class="clear"></div>';

                $break = $break + $lineend;
   $lineend;
        }
}
?>

`

HI Have a field in database which has multiple of the same number in, I have got a query of which works with the field with 1 in but then want it to then go to ++ so if I limit 9 results they will show with this query for all echoed out fields and only one result for naughtyfield =1, naughtyfield=2,naughtyfield3 etc etc up to 9

SELECT * FROM _TABLE WHERE naughtyfield=1 GROUP BY naughtyfield, field2 ORDER BY naughtyfield, field2 ASC LIMIT 9

HOpe some one can help and hope I have explained it OK , Thanks

Hi im trying to random showing items from data base and when echoed the same ones appear? If anyone could help that would be great. This is the query

SELECT * FROM TABLE1, TABLE2 WHERE FIND_IN_SET(TABLE2.FIELD1, TABLE1.FIELD1) and `FIELDNAME` >= RAND() LIMIT 10

Sorry , looks like i have sold it with

$result = mysql_query("SELECT * FROM table WHERE field LIKE '%$txt2,%' OR field = '$txt2' ORDER BY field ASC") or trigger_error(mysql_error().$sql);

So I have this query but the error is wrong syntax around '%'$txt2',%' - what would I have to change this too please... thanks
What I am looking for in the field is anything that has txt2, with things after it or just txt2 on its own without anything after it. How would I do this please alltogether.
Thanks.

$result = mysql_query("SELECT * FROM table WHERE field LIKE '%'$txt2',%' ORDER BY field ASC") or trigger_error(mysql_error().$sql);

Hi

Thanks for the reply, I have used quotes but there is no results coming back when manually I check there should be

Thanks

Hi im using FIND iN SET in a query like below, but i get an error stating that im using wrong syntax and also Warning: mysql_fetch_array() expects parameter 1 to be resource

$result = mysql_query("SELECT * FROM table FIND_IN_SET($txt2, field)") or trigger_error(mysql_error().$sql);

Hope someone can help with this, thanks

no that didnt work on either

Hi I have been using a replace array which has worked wonders but now I have just added one and it wont work why is this please. I can understand why it wont work but do not know what i have to do to sort it. I added the last one to replace a ' with a -

$old = array(' ', '/', 'amp;', ''');
$new = array('-', '-', '-', '-');

Thanks for your help.