PHP - Create Records Based On Values From Other Tables

Hi there im trying to add up the number of different values i have in a table and echo the results:

Ships
------
ShipID INT: auto count
ShipName VARCHAR: Something
Class INT: (either 1 to 4)

The class row is the one i want to count, so if i have say 5 ships; first class 1, second class 2, third class 3, fourth class 4 and fith another class 1. It would total how many of each classes i have: two class 1, one class 2, one class 3 and one class 4.

Ultimately i am going to do a similar count on another table and compare the results, but thats for later as im really not sure where to start. Ive created a query selecting all the appropriate ships to be tallied up:

Code: [Select]
}
mysql_select_db($database_swb, $swb);
$query_Ships = sprintf("SELECT * FROM ships WHERE PlayerName = %s", GetSQLValueString($colname_Ships, "text"));
$Ships = mysql_query($query_Ships, $swb) or die(mysql_error());
$row_Ships = mysql_fetch_assoc($Ships);
$totalRows_Ships = mysql_num_rows($Ships);
If you could please help me that would be ace!

Thank You

Hi. Im searching for a way to enter multiple records at once to mysql databased on a date range. Lets say i want to insert from date 2010-11-23 to date 2010-11-25 a textbox with some info and the outcome could look in database like below.

----------------------------------------------------------
| ID   |   date                |   name   |    amount   |
-----------------------------------------------------------
| 1     |   2010-11-23    |   Jhon     |        1         |
| 2     |   2010-11-24    |   Jhon     |        1         |
| 2     |   2010-11-25    |   Jhon     |        1         |
-----------------------------------------------------------

The reason i need the insertion to be like this is because if quering by month and by user to see vacation days then vacations that start in one month and end in another month can be summed by one month. If its in one record then it will pop up in both months.

Help is really welcome.

This topic has been moved to MySQL Help.

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

I want to query the five heighest values in a coloum but the coloum is not the primarykey.

i.e: i have a coloum on the end of my table (articles) called views which get updated when ever an article is viewed by adding 1. On anther page i want to be able to view the 5 most viewed articles.

I want to display all the records of table 1.  I want to inner join with table 2 so that IF there is a match between tables, table 2 data gets displayed. 

HOWEVER, if there is no match in table 2, then the corresponding record in table 1 does not even get displayed. 

Code: [Select]
$query = "select * from table1
inner join table2 on table1.id = table2.table1_id
How do I display all the records of table 2, and if that id does not match with anything on table 2, STILL display that record?

I have a db with 4 tables:
table_A
table_B
table_C
table_D


table_A contains personal info about users and has a primary id field called uid.
Each of tables B, C and D is linked to table_A via the foreign key f_uid.

table_A also has a column called signed_up which can contain a 1 or a 0.

I want to pull up lots of info from each of the 4 tables for every user, as long as the signed_up column in table_A contains a 1.

So, I've made a query like this:

Code: [Select]
$q = "SELECT
table_A . col_1 ,
table_A . col_2 ,
table_A . col_3 ,
table_B . col_1 ,
table_B . col_2 ,
table_B . col_3 ,
table_C . col_1 ,
table_C . col_2 ,
table_C . col_3 ,
table_D . col_1 ,
table_D . col_2 ,
table_D . col_3
FROM
table_A, table_B, table_C, table_D
WHERE
table_A . uid = table_B . f_uid
AND
table_A . uid = table_C . f_uid
AND
table_A . uid = table_D . f_uid
AND
table_A . signed_up = '1'
ORDER BY
table_A . some_col
";
$r = mysqli_query($dbc, $q);
The query is executing but it's also pulling up records where signed_up in table_A contains a '0'. Can anyone see a logical flaw in my query?...

Alternatively, is there an easier way I could build the query?

TIA

As part of my User Profile, I have a series of open-ended questions that Members can answer, e.g...
Quote

1.) Why did you start your own business?
2.) If you could offer one piece of advice to other entrepreneurs, what would it be?
:
10.) How do you compete against large corporations?



I was leaning towards creating a many-to-many relationship like this...

member -||---------0<- answer ->0---------||- question


From a database standpoint this works great, HOWEVER, I just realized a big problem...

If run this query...

Code: [Select]
SELECT response
FROM answer
WHERE member_id = 1;
...then I would get TEN RECORDS back and I don't know of any way to maps those to the 10 Text Boxes on the "My Thoughts" page?!

(It would be a real PITA to have to create 10 separate Prepared Queries in PHP - one for each Field - to fill out my page?!)

So, is there a way to keep my table structure, but push the 10 records from above into 10 separate variables so I would have something like $response1, $response2,... $response10 ??

Thanks,


Debbie

This topic has been moved to MySQL Help.

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

Hey,
Im needing some help with an update form that I have created using PHP/MySQL. Basically the form is working great (updating records, retrieving records and showing errors) but there is a problem with the errors.

If there are no errors and I edit the existing text input values, the database updates fine. However if I edit the existing text input values and there is an error, the edited input values dont save, they revert back to the values of the database.

I understand why this is happening, its because the text inputs are set to show the values of the database so if i edit the existing values and an error occurs or the page is refreshed, the values revert back to the database values. However id like it so that if any text in the text field have been edited and an error occurs, the text stays to how it is until the user by passes any errors and then the database is updated.

Hopefully this makes sense. I have attached the php page just incase anybody wants to look into it but any help would be much appreciated!

Also, im not looking for someone to just to fix this for me but if someone knows my problem and can guide me in the right direction, that would be great.

Looking for tutorials, but maybe the forum's quicker. Can anybody give me a loose idea how to do this?

I have a sequence of pages that the user goes through to enter information. I need to move a page up to pull data before inputting to the database - to prevent some dropped entries and errors.

Before, it was using info in the database to loop through and generate data. I want to do it just based on the info in an array:

I want array(217 => 1, 215 => 2); to loop generate a form that loops through to create something like:
Code: [Select]
<form>
Student one, Class 217<br>
First name: <input type="text" name="fname[]" id="fname" onfocus="this.className='reg_live';" onblur="this.className='reg_off';" />
Last name: <input type="text" name="fname[]" id="fname" onfocus="this.className='reg_live';" onblur="this.className='reg_off';" />

<br><hr><br>

Student one, Class 215<br>
First name: <input type="text" name="fname[]" id="fname" onfocus="this.className='reg_live';" onblur="this.className='reg_off';" />
Last name: <input type="text" name="fname[]" id="fname" onfocus="this.className='reg_live';" onblur="this.className='reg_off';" />

<br><br>

Student one, Class 215<br>
First name: <input type="text" name="fname[]" id="fname" onfocus="this.className='reg_live';" onblur="this.className='reg_off';" />
Last name: <input type="text" name="fname[]" id="fname" onfocus="this.className='reg_live';" onblur="this.className='reg_off';" />

<br><hr><br>
 and so on.

<input type="submit" value="Register" /></form>

Preferably using PHP, I'd like to create X number of colors based on X number of items and using a predefined color range. For example:

ITEM VALUE 1 1,234,567,890 2 234,567,890 3 34,567,890 4 4,567,890 5 567,890

Color Range
red = largest value
green = medium value
blue = smallest value


Example
Item 1 gets color red
Item 2 gets whatever color naturally comes between red and green
Item 3 gets color green
Item 4 gets whatever color naturally comes between green and blue
Item 5 gets color blue


Requirements
The code should be able to handle any number of items. 5 items was just an easy example.


Some Thoughts
- Number of items = number of colors
- How to divide a color range into number of items?
- Colors can be represented in decimal format: red=255,000,000 blue=000,000,255
- Can a formula be created, based on the color decimal format, to solve this problem?


I think it's the math portion of the problem that I'm stuck on right now.
Thanks for your help,

Nick

Hi all,
firstly apologies as this is a cross post from another forum and we have hit a block.. I am hoping that opening this up to another set of gurus we can get a resolution.

What I am trying to achieve is this...
I have 2 tables Main and FinancialYear. Main holds all data which I use a form to post the data to it..(all works fine). I use this code to create a drop down in the insert.php form. again this works.
Code: [Select]
<tr><td>Financial Year: xxxx/xxxx</td><td>
<!-- pulls the data from the table variable to populate the dropdown menu -->
   <?php
      $database = 'Projects_Main';
      $fintable = 'FinancialYear';
         if (!mysql_connect($db_host, $db_user, $db_pwd))
               die("Can't connect to database 'cos somethin' is wrong");
         if (!mysql_select_db($database))
             die("Can't select database");
      $result = mysql_query("SELECT FinancialYear_id, FinancialYear FROM {$fintable} order by FinancialYear");
      $options="";
         while ($row=mysql_fetch_array($result)) 
            {
               //$id=$row["FinancialYear_id"];
               $thing=$row["FinancialYear"];
               $options.="<OPTION VALUE=\"$thing\">".$thing.'</option>';
            }
   ?>
      <SELECT NAME="FinancialYear">
      <OPTION VALUE=0>Choose</OPTION>
      <?=$options?>
      </SELECT>
   </td></tr>

What I have done is built another form which list all records in the database and creates an update url for every record that passes the field Project_id where i use $_get to retrieve the Project_id to retrieve the relevant data into the update.php form.

I am able to populate the form with all the correct information BUT I am looking to introduce some dropdowns to aid updating the data and provide consistency to the data. .

Code: [Select]
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db('Projects_Main')or die("cannot select DB");

// get value of id that sent from address bar
$Project_id=$_GET['Project_id'];
//define vars
$FinancialYear=$_POST['FinancialYear'];
// other vars defined here also.. about 30

// Retrieve data from database
$sql="SELECT * FROM Main WHERE Project_id='$Project_id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
//update_record_ac.php posts the data to the dbase.
<form name="form1" method="post" action="update_record_ac.php">
<center>
<table>
<tr><td><b>Store Details<b></td></tr>
<tr><td>Financial Year:</td><td>
// takes the data from $rows and present to form
<input name="FinancialYear" type="text" id="FinancialYear" value="<?php echo $rows['FinancialYear']; ?>">

// this is where I need to create the drop down.. see my other comments in the post.....

</td></tr>


the financialYear table consists if the following;
financialyear_id - pri, auto inc. ---- data format is 2010/2011, 2011/2012....
financialyear

the main table contains 30 fields .. won't list em all...
Project_id - pri, auto inc
financialyear

I need the drop down to pull the data from the financialyear table and then to present or focus on the currently stored data...
so if the store value in the table Main is 2010/2011 if Ii was to select the update url in the list_record.php it will pull all the relevant data into update_record.php form. the financialyear field in the form should be a dropdown with all the financial years listed but the 2010/2011 is selected or focused. I still need to be able to change the entry and post this back to the table Main.....

So the dropdown contains the list of years from the financialyear table but when the record is pulled from table main the year that is stored in table Main should be highlighted in the dropdown and I should be able to select a new record and post back to the table Main..


any thoughts...

please don't slate for the cross post, I haven't sanatised the data at any stage. I know i'm open to injection attacks. and yes my code is a little dirty... all these will be rectified as i finalise the process and ensure the consept works.

Thanks for taking the time to read and hopefully you are able to understand the requirement and are able to assist.

thanks
Balgrath

Hello, I'm embarking on a pretty ambitious task and I need some bits of information here and there. One of the functions I need to achieve is to build a query or array of photos based on a background image and user input.    So imagine that I have a box  and within that box is a column of three rows.    I need to have three different bits of data be placed into each of the subsequent rows and then an image is taken of these three pieces of data overlaying the background photo. Then stored somewhere with an incremented identifier to be pulled later.    I think I can already begin to imagine how it would work but what eludes me is a "screenshot" function to generate the images. I'm looking for .png or .jpg end result files with fixed width/height and item placement.    Thank you for any help 

I am trying to create seperate tables from this. Maybe a div class will work. I want each cat variable 'catname' to be in seperate tables at the top of each table with the subcats for that cat in 1 or 2 rows under the cat. I want to be able to also choose whether to use 1 or 2 table rows for the subcats under the cat. any ideas? Will appreciate your help.



// Categories
$sql = "SELECT catid, catname AS catname FROM $t_cats WHERE enabled = '1' $sortcatsql";
$rescats = mysql_query($sql) or die(mysql_error());
$catcount = @mysql_num_rows($rescats);

$percol_short = floor($catcount/$dir_cols);
$percol_long = $percol_short+1;
$longcols = $catcount%$dir_cols;

$i = 0;
$j = 0;
$col = 0;
$thiscolcats = 0;

while($rowcat=mysql_fetch_array($rescats))
{
if ($j >= $thiscolcats)
{
$col++;
$thiscolcats = ($col > $longcols) ? $percol_short : $percol_long;
$j = 0;

echo "<td valign=\"top\" width=\"$cell_width%\">";
}

$i++;
$j++;


    $catlink = buildURL("ads", array($xcityid, $rowcat['catid'], $rowcat['catname']));


$adcount = 0+$catadcounts[$rowcat['catid']];

?>

<table border="0" cellspacing="0" cellpadding="0" width="100%" class="dir_cat">
<tr>
<th width="25" valign="top"><img src="images/category.gif" border="0" align="absmiddle"></th>
<th><a href="<?php echo $catlink; ?>"><?php echo $rowcat['catname']; ?></a>
<?php if($show_cat_adcount) { ?><span class="count">(<?php echo $adcount; ?>)</span><?php } ?>
</th>
</tr>

<?php

$sql = "SELECT scat.subcatid, scat.subcatname AS subcatname
FROM $t_subcats scat
WHERE scat.catid = $rowcat[catid]
AND scat.enabled = '1'
$sortsubcatsql";

$ressubcats = mysql_query($sql) or die(mysql_error()."<br>$sql");

$subcatcount = mysql_num_rows($ressubcats);


while ($rowsubcat = mysql_fetch_array($ressubcats))
{

    if ($shortcut_categories && $subcatcount == 1 
            && $rowsubcat['subcatname'] == $rowcat['catname']) {
        continue;
    }

    
$adcount = 0+$subcatadcounts[$rowsubcat['subcatid']];


        $subcat_url = buildURL("ads", array($xcityid, $rowcat['catid'], $rowcat['catname'], 
            $rowsubcat['subcatid'], $rowsubcat['subcatname']));


?>
<tr>
<td>&nbsp;</td>
<td>
<a href="<?php echo $subcat_url; ?>"><?php echo $rowsubcat['subcatname']; ?></a>
<?php if($show_subcat_adcount) { ?><span class="count">(<?php echo $adcount; ?>)</span><?php } ?>
<br>
</td>
</tr>

<?php

}

?>

</table>
<br>

<?php

if($j==$thiscolcats || $i==$catcount) echo "</td>";

}


?>

</tr></table>

Hi,

I have an array filled with values like this:

Code: [Select]
string(1) "Acer 1"
string(2) "Acer 2"
string(3) "Dell 1"
string(4) "Dell 2"
string(5) "Dell 3"
string(6) "Dell 4"
string(7) "Acer 3"
string(8) "Acer 4"
Is there a way to successfully remove all array strings that starts with a given value like "Acer"?

I tried with this snippet, does not work:

Code: [Select]
foreach($array as &$value) {

  if(preg_match("/^Acer/", $value)) {
  unset($value);
} }

Any ideas will be appreciated!